473,840 Members | 1,602 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Top N rows, discard duplicate

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
4 3758
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
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
(me*******@yaho o.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2340
by: TonyB | last post by:
Hi, I've searched the group and need more information and guidance on this issue I need to resolve next week. I work for the local school system and I am working on a way to parse a CSV file of class lists from MS Excel. It is a ~2MB file with all teachers and all their classes in it. I thought I would use Python to do this since many people I know tell me how great it is. The real sticking point is there are duplicates of each...
1
1789
by: Asha | last post by:
greetings, does anyone have any idea how to delete duplicate rows in a dataset gracefully (the fast and easy way)
12
2015
by: Graham Blandford | last post by:
Hi all, Would someone be able to tell me the most 'graceful' way of removing unwanted rows from a dataset based on a condition prior to update? OR, resetting the rows all to unchanged after they are initally added to the recordset. I create a dataset, which begins empty after the initial .Fill. Then I create several rows with some default information, leaving one
7
9786
by: Jon Maz | last post by:
Hi, I have a MySql problem I hope someone can help me with. I'm trying to run an update on a linking table, the update is running into a Primary Key constraint violation, and in my workaround I've got stuck trying to write a DELETE statement. Here's the table I'm working on: CREATE TABLE `articles_categories` (
0
2110
by: B.N.Prabhu | last post by:
Hi, I have a DataTable with several rows. Its having 20 Columns. when i click the Insert button then i have to check the Database Rows. Whether these new rows are already available in the Database. If its there, then i need to seperate the Duplicate Records Based upon 4 columns(EmployeeID, ProjectName, ProjectType, StartTime -- should be activate as a Composite Primary key)
5
4586
by: kutty | last post by:
Hi All, I am loading data to a child table from a text file. the text files also contains data not referenced by parent key. while loading the data if one row fails to satisfies the constraint everything is getting rollback.. plz suggest me something.. which will help me to discard the unsatisfied rows and continue with the rest..
5
8436
jamesd0142
by: jamesd0142 | last post by:
My manager and I where looking at some complex code to eliminate duplicate records in a database table. then it hit me how its done easily... so i thought i'd share it... In English: -------------------------------- -- Eliminating Duplicate rows -- -------------------------------- -- select all into a temp table. -- truncate origional table.
1
1345
by: rfeio | last post by:
Hi! I have a table with several rows, some of which I need to duplicate. The fields of table1 are field1, field2, field3 and field4. Field1 is automatically incremented, field2 will serve as a filter condition. Normally to duplicate the rows that matched a certain condition I would do: INSERT INTO table1 (field2, field3, field4) SELECT field2, field3, field4 WHERE field2=x
4
5474
by: ravir81 | last post by:
Hi, I am currently working on excel validation using Perl. I am new to Excel validation but not for Perl. I have a question regarding one of the validation. Could anyone please tell me how to get the number of duplicate rows based on a particular cell value of each these duplicate rows. I mean all the cell values of a row will not be duplicated but a individual columns cell value will be duplicated and I need to create a separate excel with...
0
9699
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10924
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10605
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10665
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9444
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7023
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5874
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4498
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4078
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.