March 3, 2009 at 1:00 am
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.
March 3, 2009 at 6:02 am
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
March 3, 2009 at 6:39 pm
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
March 3, 2009 at 7:19 pm
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
March 3, 2009 at 8:11 pm
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
March 4, 2009 at 5:55 am
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
March 4, 2009 at 6:47 am
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"
March 4, 2009 at 6:54 pm
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
March 13, 2009 at 10:04 am
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
March 13, 2009 at 10:42 pm
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
March 14, 2009 at 1:12 pm
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.
March 14, 2009 at 2:37 pm
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply