By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,077 Members | 1,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,077 IT Pros & Developers. It's quick & easy.

Top N rows, discard duplicate

P: n/a
I have data in table as follows,

Num Category ProductName
---- -------- -----------
100 A Product1
100 B Product1

101 A Product2
101 B Product2
101 A Product2
101 B Product2

102 A Product3
102 B Product3
102 C Product3
102 D Product3

Can sb pl help to what t-sql query to use to achieve as follows,
for some reason, there are duplicate rows, but i need to get
top most rows of category A, B as follows.

Num Category ProductName
---- -------- -----------
100 A Product1
100 B Product1

101 A Product2
101 B Product2

102 A Product3
102 B Product3

TIA
MeDhanush

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
What does "top most rows" mean? For example, why was "102 C Product3"
excluded from your example result? You can't reliably query rows based on
some notion of order (just the order you wrote them down in?) unless that
order is somehow represented in the table.

--
David Portas
SQL Server MVP
--
Jul 23 '05 #2

P: n/a
David,
thxs for the reply.

A is result of first web service call
B is result of second web service call
C is result of third web service call
D is result of fourth web service call

Some times first and second web service are re-submitted.
as a result we have duplicate rows.
And we need to generate report from results of web service responses A
and B.
I'm sure the data is not in Normalized fashion.
Thanks
Kishore

David Portas wrote:
What does "top most rows" mean? For example, why was "102 C Product3"
excluded from your example result? You can't reliably query rows based on
some notion of order (just the order you wrote them down in?) unless that
order is somehow represented in the table.

--
David Portas
SQL Server MVP
--


Jul 23 '05 #3

P: n/a
(me*******@yahoo.com) writes:
Can sb pl help to what t-sql query to use to achieve as follows,
for some reason, there are duplicate rows, but i need to get
top most rows of category A, B as follows.

Num Category ProductName
---- -------- -----------
100 A Product1
100 B Product1

101 A Product2
101 B Product2

102 A Product3
102 B Product3


There is no such thing as a "top-most" row in a table. A table is an
orderd set.

It seems that with the sample data you have given that this would do:

SELECT DISTINCT Num, Category, ProductName
FROM tbl
WHERE Category IN ('A', 'B')

If product names are different, you can do:

SELECT Num, Category, MAX(ProductName)
FROM tbl
WHERE Category IN ('A', 'B')
GROUP BY Num, Category


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

P: n/a
Unless you've stored the information about which came first then you won't
be able to do it. SQL Server doesn't retain that information for you.

--
David Portas
SQL Server MVP
--
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.