Select distinct row on SQL 2000. Please help me!

Question

Select distinct row on SQL 2000. Please help me!

tuyetmuadong

SSC Enthusiast

Points: 123
More actions
March 3, 2009 at 1:00 am

#135133

Hi everybody,
I want to distinct value on one column, but get all column of data. How is query in SQL 2000 ?
For example: I have a table PK003_INDEX(LINK, MTE_TYPE, MTE_NUM)
link ---mte_type---mte_num
1 AAA CA
1 TRG FL
2 TRG TFS
2 DFG EFB
3 TRG CA
I'd link result is
link ---mte_type---mte_num
1 AAA CA
2 TRG TFS
3 TRG CA
I mean I want to distinct on LINK field, anything result on the other fields.
How is query in SQL 2000 ?
Thank you for you help.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

Well, what you've really defined is a DISTINCT on both fields. You're getting all values that match a list for both criteria. It's not really a unique listing of the first column.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

tuyetmuadong SSC Enthusiast Points: 123 More actions · Answer 2

Thank you, Grant Fritchey.

Yes. All fields in table are not unique so that I want to get value that not duplicate on LINK field ( whatever data other fields ) but pull up all field in query.

I tried query "select distinct * from tablename" but it didn't work.

Thank you

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

tuyetmuadong (3/3/2009)
Hi everybody,
I want to distinct value on one column, but get all column of data. How is query in SQL 2000 ?
For example: I have a table PK003_INDEX(LINK, MTE_TYPE, MTE_NUM)
link ---mte_type---mte_num
1 AAA CA
1 TRG FL
2 TRG TFS
2 DFG EFB
3 TRG CA
I'd link result is
link ---mte_type---mte_num
1 AAA CA
2 TRG TFS
3 TRG CA
I mean I want to distinct on LINK field, anything result on the other fields.
How is query in SQL 2000 ?
Thank you for you help.

So why did you select TRF and TFS for Link 2? And that is the clustered index on this table? Just need a little more info to solve this...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

tuyetmuadong SSC Enthusiast Points: 123 More actions · Answer 4

This result is my demo. Actually, we can get (2, TRG, TFS) or (2, DFG, EFB). That is ok. I only care value of LINK field, I don't care value of mte_num and mte_type.

There is no primary key, no index clustered on my table.

Thank you

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

It is bad, really bad, form to have tables without primary keys. You need a way to identify data in order to retrieve it.

The problem is, in your example, you have a link value of 1 listed twice, once with mte_type of 'AAA' and another of 'TRG'. If you do a distinct list, you'll get two copies of link value 1, have to. You also can't join to eliminate duplicates or GROUP BY to eliminate duplicates because, each of those combinations is unique. You could do something like this (untested psuedocode because I don't have your structure)

SELECT b.link

,a.mte_type

,a.mte_num

FROM tablename a

JOIN (SELECT TOP 1 b2.link, b2.mte_type

FROM tablename b2

WHERE b2.link = a.link

ORDER BY b2.mte_type) b

ON a.link = b.link and a.mte_type = b.mte_type

Or maybe you could do that in WHERE clause instead of in a JOIN... I'm not sure. I still don't understand what your structure is meant to do and what data you're attempting to get from it?

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

321 MySQL Right there with Babe Points: 750 More actions · Answer 6

tuyetmuadong (3/3/2009)
link ---mte_type---mte_num
1 AAA CA
1 TRG FL
2 TRG TFS
2 DFG EFB
3 TRG CA
I'd link result is
link ---mte_type---mte_num
1 AAA CA
2 TRG TFS
3 TRG CA
I mean I want to distinct on LINK field, anything result on the other fields.
How is query in SQL 2000 ?
Thank you for you help.

i hope i understand your prob. try select link, min(mte_type),min(mte_num) from table group by link

min or max is equal if you don´t need this two values "anything result on the other fields"

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

tuyetmuadong (3/3/2009)
This result is my demo. Actually, we can get (2, TRG, TFS) or (2, DFG, EFB). That is ok. I only care value of LINK field, I don't care value of mte_num and mte_type.
There is no primary key, no index clustered on my table.
Thank you

Here is a solution that works in 2k5... in return, please explain why you don't care what's in the other two columns but still need to return something in them. Thanks.

Oh yeah... read the comments in the code below... it's how we'd like to see test data and table descriptions for posts to help save us a little time. Thank you for your consideration.

--===== Conditionally drop the temporary test table.

-- This is not a part of the solution. It's to demo it.

IF OBJECT_ID('TempDB..#YourTable') IS NOT NULL

DROP TABLE #YourTable

--===== Create the temporary test table.

-- This is not a part of the solution. It's to demo it.

CREATE TABLE #YourTable

(

Link INT,

MTE_Type CHAR(3),

MTE_Num VARCHAR(3)

)

--===== Populate the temporary test table with the posted data.

-- This is not a part of the solution. It's to demo it.

INSERT INTO #YourTable

(Link, MTE_Type, MTE_Num)

SELECT '1','AAA','CA' UNION ALL

SELECT '1','TRG','FL' UNION ALL

SELECT '2','TRG','TFS' UNION ALL

SELECT '2','DFG','EFB' UNION ALL

SELECT '3','TRG','CA'

--===== And, finally, we get to the tested solution...

;WITH cteNumberThem AS

(

SELECT ROW_NUMBER() OVER (PARTITION BY Link ORDER BY Link,MTE_Type,MTE_Num) AS RowNum,

Link, MTE_Type, MTE_Num

FROM #YourTable

)

SELECT Link, MTE_Type, MTE_Num

FROM cteNumberThem

WHERE RowNum = 1

As a sidebar, not having the right indexes will cause the PARTITION BY and ORDER BY to take quite some time on large tables. Lemme know how it works out for you and, please, take the time to answer my question... this is a two way forum and I like to learn these things. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Farrokh Tabatabai SSC Enthusiast Points: 152 More actions · Answer 8

I was looking for something similar to this post as well and your solution is the best - a keeper for future reference. Thank you very much. FT

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 9

There are many solutions on this thread, so we're not sure which solution you're talking about.

While I "have you on the phone", can you tell me what the business requirements are for such a thing? What you're doing with it? I ask simply because I don't understand the need for it and would like to know. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Farrokh Tabatabai SSC Enthusiast Points: 152 More actions · Answer 10

Jeff,

I was talking about your solution:

SELECT ROW_NUMBER() OVER (PARTITION BY Link ORDER BY Li....

Others are not as good.

As for reason for this, when I was asked for it I, had exactly the same reaction. Why?

Then I thought after leaving work, while driving that it was actually a bad question and that was why there was not a good answer for it (basically the question did not make senses!)

I work in an immunological (organ transplantation as statitistician/developer) lab and there are always dificult questions to be answered by clinicians regarding patient/donor information.

This one was as follows: a table holding many different tests' results has results in from one to dozens of tests. Each test (record) is tagged with the sample's ID. Someone wanted a list based on a complex logic (where cluase) but with distinct ID (no more than one record per ID), but wanted other information as well which made the query not doing this condition (1 record/ID). I managed to make folks get over the bad question, but still wanted to see if there was a good way of doing this. I was thinking about the TOP 1 in select statement using grouping and decided to do a search on sqlcentral and found your method and liked it better.

Anyway, I hope this answers your question as to why.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Ahh... got it. Thanks. Glad you were able to get them over the bad question. You could, however, use dynamic SQL to create a cross-tab should that bad question ever arise again. The technique is explained in the following article.

http://qa.sqlservercentral.com/articles/cross+tab/65048/

Now you know why I asked "why". There's always a solution even if it's for the kinds of bad questions folks asked you to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions