get second record when having duplicate values for a column

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi experts,
I have a discount table looks like this:

SQL

table discount ("productId","lineNum","price","discount","toDate","fromDate")

Values could be something like this:

SQL

("product1","0","100","10","2015-03-01","2015-04-30")
("product1","1","100","10","2015-05-01","2015-06-30")

So i could have the same product with different lineNum.
What i intend to do is: select from my table all the products that have discount between 2 dates (which i could easily do). AND if the product is repeating take only the product with lineNum=1
I can't figure out this part!
EDIT: What if there is no date range? distinct won't work in this case because every line is distinct.

Any help will be so much appreciated.
Thanks in advance
Samira

Posted 29-Apr-15 5:37am

Samira Radwan

Updated 29-Apr-15 6:07am

v2

Add a Solution

Comments

CHill60 29-Apr-15 11:48am

Try something like SELECT TOP 1 FROM discount WHERE @InDate BETWEEN toDate AND fromDate ORDER BY lineNum.
[Edit] - Nope - that won't work - sorry. Try using GROUP BY productId

Maciej Los 29-Apr-15 14:34pm

So... in your example, the record which meet your needs is the second one. Am i right?

2 solutions

Solution 1

Try distinct

SELECT distinct productid, discount,price  FROM discount WHERE @InDate BETWEEN toDate AND fromDate ORDER BY lineNum

Posted 29-Apr-15 5:56am

Manoj Kumar Mandal

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-04-29T08:40:00

Solution 2

If i understand you correctly, you need to use ROW_NUMBER()[^] ranking function.

If lineNum starts from 0 try this:

SQL

SELECT productId , lineNum, price, discount, toDate, fromDate
FROM (
    SELECT productId , lineNum, price, discount, toDate, fromDate, ROW_NUMBER() OVER(PARTITION BY productId ORDER BY lineNum)
    FROM TableName
    WHERE fromDate >= @fromDate AND toData <= @toDate AND lineNum > 0
    ) AS T 
WHERE RowNo=1

Posted 29-Apr-15 8:40am

Maciej Los

Comments

Samira Radwan 29-Apr-15 15:14pm

thanks a lot!

Maciej Los 29-Apr-15 15:23pm

You're very welcome ;)

Sascha Lefèvre 29-Apr-15 17:32pm

I think this query doesn't fulfill the requirement "AND if the product is repeating take only the product with lineNum=1" - because it will not return products that only have a record with lineNum=0

Maciej Los 30-Apr-15 1:43am

Sascha, thank you for your comment, but have a look at my first [where] statement (after second [and]).

Sascha Lefèvre 30-Apr-15 20:26pm

Please take a look here, Maciej: http://sqlfiddle.com/#!6/2f64e/1
I don't have a better solution.. can you come up with one? I would be very interested :-)
/Sascha

Maciej Los 1-May-15 16:13pm

OK, i'll look at it tommorow.

Samira Radwan 30-Apr-15 8:57am

your code works for me, just i had to modify it and add (as t1) in the second select otherwise it will give error "no column name was specified for column 7 of T"
and RowNo is lineNum?
thanks again

Maciej Los 30-Apr-15 8:58am

You're very welcome :)

Samira Radwan 30-Apr-15 9:21am

thanks Maciej.
i have just tested it again (if ToDate is null which means no ending date for the discount)and it ignores the records with linenum=0.
maybe i'm doing something wrong. please guide me :)

Maciej Los 30-Apr-15 11:09am

Sorry, i do not see your data. Try to add another condition: NOT DateField IS NULL.