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
4  3757 
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
--
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
--
(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
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
--
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
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...
|
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)
|
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
|
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` (
|
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)
| | |
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..
|
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.
|
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
|
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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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...
| | |
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...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
