SQL DISTINCT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL DISTINCT

Author

Topic

Iguana
Starting Member

5 Posts

Posted - 2009-10-28 : 07:02:01

Hi people,

I am struggling with SQL DISTINCT.

Basically I have 1 table with the following columns:

ID, Reference Firtname, Surname and a few other columns.

What I am trying to do is return all the rows in the table that match a variable and have a unique Reference number. So that if there are duplicate records then only the latest ones will be returned along with all other rows which match the variable.

For example, if the varaible is 'simon' then return all rows (and all columns) with firstname or surname simon but only the rows where the reference is unique.

This is where I have got to so far but it jsut returns all the rows??

SELECT * FROM tbl_test WHERE reference IN
(
SELECT DISTINCT reference
FROM tbl_test WHERE firstnames LIKE 'variable' OR surname LIKE 'variable'
)
ORDER BY reference DESC

Any help or pointers would be greatly appreciated

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-28 : 08:10:05

I don't fully understand what you're after here. Please post some sample data and expected output and you should get an answer shortly.

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-28 : 08:37:38

SELECT DISTINCT reference
FROM tbl_test WHERE firstnames LIKE 'variable' OR surname LIKE 'variable'
ORDER BY reference DESC

Madhivanan

Failing to plan is Planning to fail

Iguana
Starting Member

5 Posts

Posted - 2009-10-28 : 10:30:01

Him JimF, and madhivanan,

Firstly, Madhivanan, thanks for the reply but that is not quite what i'm after. Basically it does work but only returns the reference column. I need to return all the column values for that row, not just the reference column.

Jim,Thanks for the reply,
I am trying to search all the rows in the dtabase where the reference column matches the variable (which is a number) and display all the resulting rows that match as below:

ID REFERENCE FIRSTNAME SURNAME
1 E1234 Joe Bloggs
2 E3928 Joe Black
3 E1234 Joe Bloggs

If the variable was 'Joe' then I want the query to return only the latest row that matches the variable 'Joe'

so it would return as follows:

ID REFERENCE FIRSTNAME SURNAME
2 E3928 Joe Black
3 E1234 Joe Bloggs

the record for ID-1 would not be returned as I only want one record (the latest one) with the unique REFERENCE number.

hope this makes sense. Thanks for your time.

Tahumars
Starting Member

15 Posts

Posted - 2009-10-28 : 17:55:51

I think what you're after is something along the lines of:

SELECT * FROM tbl_test WHERE reference IN 
(
SELECT TOP 1 reference 
FROM tbl_test WHERE firstnames LIKE 'variable' OR surname LIKE 'variable'
order by id desc
)
ORDER BY reference DESC

DISTINCT reference would give you all of the references that match the variable as long as they are different from each other. What you want to do is just return the bottom one (if I'm understanding correctly). Using TOP and ordering in reverse should give you what you're looking for.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-29 : 02:19:40

quote:
Originally posted by Iguana

Him JimF, and madhivanan,

Firstly, Madhivanan, thanks for the reply but that is not quite what i'm after. Basically it does work but only returns the reference column. I need to return all the column values for that row, not just the reference column.

Jim,Thanks for the reply,
I am trying to search all the rows in the dtabase where the reference column matches the variable (which is a number) and display all the resulting rows that match as below:

ID REFERENCE FIRSTNAME SURNAME
1 E1234 Joe Bloggs
2 E3928 Joe Black
3 E1234 Joe Bloggs

If the variable was 'Joe' then I want the query to return only the latest row that matches the variable 'Joe'

so it would return as follows:

ID REFERENCE FIRSTNAME SURNAME
2 E3928 Joe Black
3 E1234 Joe Bloggs

the record for ID-1 would not be returned as I only want one record (the latest one) with the unique REFERENCE number.

hope this makes sense. Thanks for your time.

You need this

select t1.* from your_table as t1 inner join
(
select max(ID) as ID, REFERENCE from your_table
group by REFERENCE
) as t2 on t1.ID=t2.ID and t1.REFERENCE=t2.REFERENCE

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources