help with SQL Query To select distinct records

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

SQL Server 2005 Forums

Transact-SQL (2005)

help with SQL Query To select distinct records

Author

Topic

Harris00
Starting Member

19 Posts

Posted - 2009-05-07 : 17:01:46

hello

I have a table with below columns and i noticed in my data the records are being repeated if client has more than one address like below

id name pin dob gender streetadd
1 joe 234 12/1/2000 m 123 test st
1 joe 234 12/1/2000 m 456 lost dr
2 karen 345 1/1/1988 f 34 ditch ct
3 smith 565 3/1/1999 m 987 first st
3 smith 565 3/1/1999 m 564 second st

i have below query
select id,name,pin,dob,gender,streetadd from client group by id,name,pin,dob,gender,streetadd

and i get all above records but i need one record per id
the desired results should look like below

id name pin dob gender streetadd
1 joe 234 12/1/2000 m 456 lost dr
2 karen 345 1/1/1988 f 34 ditch ct
3 smith 565 3/1/1999 m 564 second st

how should i write the sql the give above results?

nhess80
Yak Posting Veteran

83 Posts

Posted - 2009-05-07 : 17:46:13

If you write it like this and remove the streetadd from the query it will display fine. Or do you need it to display at least one of the addresses

select id,name,pin,dob,gender
from client
group by id,name,pin,dob,gender

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-07 : 17:58:28

You'll have to figure out how you want to detiermine which row comes first, but this might help you:

SELECT 
	id,
	name,
	pin,
	dob,
	gender,
	streetadd
FROM
	(
		SELECT
			id,
			name,
			pin,
			dob,
			gender,
			streetadd,
			ROW_NUMBER() OVER (PARTITION BY id ORDER BY streetadd) AS RowNum
			-- Not sure about the ORRDER BY as it wasn't specified.
		FROM
			Client
	) AS T
WHERE
	RowNum = 1

Harris00
Starting Member

19 Posts

Posted - 2009-05-08 : 10:01:34

i would like to get at least one address back, the last row's would be perfect.

Harris00
Starting Member

19 Posts

Posted - 2009-05-08 : 10:40:32

Lamprey - Your query returning me error with message Incorrect Syntax near "("

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-08 : 10:43:34

The query looks fine.
Maybe you do not use SQL Server 2005?

No, you're never too old to Yak'n'Roll if you're too young to die.

Harris00
Starting Member

19 Posts

Posted - 2009-05-08 : 10:44:35

lamprey - i got it working. Thanks alot.

Harris00
Starting Member

19 Posts

Posted - 2009-05-08 : 10:48:14

lamprey

how can i get record with max row number from above query. In where you have RowNum=1 how can i say where Row Num is Max?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 10:54:55

quote:
Originally posted by Harris00

i would like to get at least one address back, the last row's would be perfect.

last based on which column value? there's no concept of first and last in sql table unless you specify order based on a unique valued column.

Harris00
Starting Member

19 Posts

Posted - 2009-05-08 : 11:54:09

The query below gives me what i need but it gives the data from first row but i would like to get the data from max row for that id.
SELECT
id,
name,
pin,
dob,
gender,
streetadd
FROM
(
SELECT
id,
name,
pin,
dob,
gender,
streetadd,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY streetadd) AS RowNum
-- Not sure about the ORRDER BY as it wasn't specified.
FROM
Client
) AS T
WHERE
RowNum = 1

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-08 : 11:57:52

Not sure what you mean by MAX row? MAX of what Date or...? Here is a possible solution..

quote:
Originally posted by Harris00

The query below gives me what i need but it gives the data from first row but i would like to get the data from max row for that id.
SELECT
id,
name,
pin,
dob,
gender,
streetadd
FROM
(
SELECT
id,
name,
pin,
dob,
gender,
streetadd,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY streetadd DESC) AS RowNum
-- Not sure about the ORRDER BY as it wasn't specified.
FROM
Client
) AS T
WHERE
RowNum = 1

Harris00
Starting Member

19 Posts

Posted - 2009-05-08 : 12:26:11

Right now if i run below query i get a RowNum column which gives 1,2,3 for each row.

SELECT
id,
name,
pin,
dob,
gender,
streetadd,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY streetadd DESC) AS RowNum
-- Not sure about the ORRDER BY as it wasn't specified.
FROM
Client

In your outer query the clause WHERE RowNum=1 returns record where RowNum=1, but i would need to have it return RowNum=Max for that Id.
i.e

id name pin dob gender streetadd rownum
1 joe 234 12/1/2000 m 123 test st 1
1 joe 234 12/1/2000 m 456 lost dr 2
2 karen 345 1/1/1988 f 34 ditch ct 1
3 smith 565 3/1/1999 m 987 first st 1
3 smith 565 3/1/1999 m 564 second st 2
3 smith 565 3/1/1999 m 343 third st 3

I would like to get below records back

id name pin dob gender streetadd rownum
1 joe 234 12/1/2000 m 456 lost dr 2
2 karen 345 1/1/1988 f 34 ditch ct 1
3 smith 565 3/1/1999 m 343 third st 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 12:47:41

shouldnt your original posted query give you what you want in that case? i.e using ORDER BY streetadd along with PARTITION

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-08 : 13:23:49

Ok, I gave you one query and you said you wanted the max. I then gave you the solution for that (with the DESC on the streetadd). You still claimed that is not what you want, but clearly gives the desired outpout that you posted on 05/08/2009 : 12:26:11. I've reproduced each query back to back so we see what each one produces:

-- Setup Data
DECLARE @Client TABLE 
(
	id int, 
	name VARCHAR(50),
	pin INT,
	dob DATETIME,
	gender CHAR(1),
	streetadd VARCHAR(50)
)

INSERT @Client
SELECT 1, 'joe', 234, '12/1/2000', 'm', '123 test st'
UNION ALL SELECT 1, 'joe', 234, '12/1/2000', 'm', '456 lost dr'
UNION ALL SELECT 2, 'karen', 345, '1/1/1988', 'f', '34 ditch ct'
UNION ALL SELECT 3, 'smith', 565, '3/1/1999', 'm', '987 first st'
UNION ALL SELECT 3, 'smith', 565, '3/1/1999', 'm', '564 second st'
UNION ALL SELECT 3, 'smith', 565, '3/1/1999', 'm', '343 third st'

-- Query 1
SELECT
	id,
	name,
	pin,
	dob,
	gender,
	streetadd,
	ROW_NUMBER() OVER (PARTITION BY id ORDER BY streetadd ASC) AS RowNum
FROM
	@Client

-- Query 2
SELECT
	id,
	name,
	pin,
	dob,
	gender,
	streetadd,
	ROW_NUMBER() OVER (PARTITION BY id ORDER BY streetadd DESC) AS RowNum
FROM
	@Client

If you select from either query where RowNum = 1 do you get the desired results from one of them?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-08 : 13:29:32

Perhaps I spoke too fast:

here is the data you say is returned:

id name pin dob gender streetadd rownum
1 joe 234 12/1/2000 m 123 test st 1
1 joe 234 12/1/2000 m 456 lost dr 2
2 karen 345 1/1/1988 f 34 ditch ct 1
3 smith 565 3/1/1999 m 987 first st 1
3 smith 565 3/1/1999 m 564 second st 2
3 smith 565 3/1/1999 m 343 third st 3

But there is an issue with the sorting.. Either ID 1 is wrong or ID 3 is wrong if you are sorting by STREETADD.

ID 1 is storted ASCENDING and ID 3 is sorted DESCENDING. Which is correct or is there more criteria you have not mentioned?

Harris00
Starting Member

19 Posts

Posted - 2009-05-11 : 09:31:12

I did both sort ASC and DESC but getting the same results. If a client has two or more different address i want to show the address on the last row or may be the last row of that client's record

In below example

id name pin dob gender streetadd rownum
1 joe 234 12/1/2000 m 123 test st 1
1 joe 234 12/1/2000 m 456 lost dr 2
2 karen 345 1/1/1988 f 34 ditch ct 1
3 smith 565 3/1/1999 m 987 first st 1
3 smith 565 3/1/1999 m 564 second st 2
3 smith 565 3/1/1999 m 343 third st 3

the desired result should be

1 joe 234 12/1/2000 m 456 lost dr 2
2 karen 345 1/1/1988 f 34 ditch ct 1
3 smith 565 3/1/1999 m 343 third st 3

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-11 : 09:49:32

Harris,

You need to understand that in the database / table context, there isn't such things as FIRST ROW or LAST ROW. Records are not stored in the table in any particular sequence. You define how the data are being retrieve with your SELECT statement and the sequence of record being return with the ORDER BY clause with ASC or DESC.

quote:
If a client has two or more different address i want to show the address on the last row or may be the last row of that client's record

So basically you have to use ORDER BY <column(s)> and sort it to get your desired sequence as what Lamprey as demonstrated in his query

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources