Help with a distinct query - 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

Help with a distinct query

Author

Topic

ncdesigner
Starting Member

1 Post

Posted - 2007-11-12 : 03:26:02

Hello everybody

I'm kind of newbie and I like to know if you can solve me a question,

The fact is I'm trying to gather some information and I don't know how. What I want to do is retrieve the values of five columns, the first one is the primary key, but the others must be unique (all together)

PK Value1 Value2 Value3 Value4
1 3 5 6 3
2 3 5 7 2
3 4 3 1 4
4 3 5 6 3 <- =PK1

I've tried with:

SELECT MesuraA, MesuraB, MesuraC, MesuraD
FROM (SELECT DISTINCT MesuraA, MesuraB, MesuraC, MesuraD
FROM barres_Ciampalini) AS derivedtbl_1

But I don't know how to retrieve the Id.

How Can I do that?

Thanks for read

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 03:32:39

SELECT MIN(ID) AS Id, MeasureA, MeasureB, MeasureC, MeasureD
FROM barres_Ciampalini
GROUP BY MeasureA, MeasureB, MeasureC, MeasureD

E 12°55'05.25"
N 56°04'39.16"

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-12 : 04:09:35

quote:
Originally posted by Peso

SELECT MIN(ID) AS Id, MeasureA, MeasureB, MeasureC, MeasureD
FROM barres_Ciampalini
GROUP BY MeasureA, MeasureB, MeasureC, MeasureD

E 12°55'05.25"
N 56°04'39.16"

to get your desired pattern make an exra effort of doing order by Id at the end of the query defined obove else it will sort data by first column defined first in group by clause

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 05:06:38

Why do you think ordering is important?
The original poster did not ask to sort...

E 12°55'05.25"
N 56°04'39.16"

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-12 : 07:09:43

quote:
Originally posted by Peso

Why do you think ordering is important?
The original poster did not ask to sort...

E 12°55'05.25"
N 56°04'39.16"

that is just for the poster to get the data in the pattren he has shown in sample

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-11-12 : 10:13:23

Best practice is to avoid ordering on the database side. That is a presentation issue that should be handled by the client app.

e4 d5 xd5 Nf6

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-11-12 : 13:44:22

I'm not 100% what the desired results are, but is this close to what you are trying to do?

DECLARE @Table TABLE (PK INT, Val1 INT, Val2 INT, Val3 INT, Val4 INT)

INSERT @Table
SELECT 1, 3, 5, 6, 3
UNION ALL SELECT 2, 3, 5, 7, 2
UNION ALL SELECT 3, 4, 3, 1, 4
UNION ALL SELECT 4, 3, 5, 6, 3

SELECT 
	T.*
FROM 
	@Table AS T
INNER JOIN
	(
		SELECT 
			Val1,
			Val2,
			Val3,
			Val4
		FROM 
			@Table 
		GROUP BY
			Val1,
			Val2,
			Val3,
			Val4
		HAVING 
			COUNT(*) = 1
	) AS NoDup
	ON T.Val1 = NoDup.Val1
	AND T.Val2 = NoDup.Val2
	AND T.Val3 = NoDup.Val3
	AND T.Val4 = NoDup.Val4

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 14:03:52

[code]SELECT d.PK,
d.Val1,
d.Val2,
d.Val3,
d.Val4
FROM (
SELECT PK,
Val1,
Val2,
Val3,
Val4,
COUNT(*) OVER (PARTITION BY Val1, Val2, Val3, Val4) AS Items
FROM @Table
) AS d
WHERE d.Items = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-11-12 : 17:14:27

quote:
Originally posted by arorarahul.0688

to get your desired pattern make an exra effort of doing order by Id at the end of the query defined obove else it will sort data by first column defined first in group by clause

It might look like that's true but it is not necessarily. If you want a sort order then specify it, otherwise don't assume it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources