SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with a distinct query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ncdesigner
Starting Member

1 Posts

Posted - 11/12/2007 :  03:26:02  Show Profile  Reply with Quote
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

Sweden
30114 Posts

Posted - 11/12/2007 :  03:32:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 11/12/2007 03:33:25
Go to Top of Page

arorarahul.0688
Posting Yak Master

India
125 Posts

Posted - 11/12/2007 :  04:09:35  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 11/12/2007 :  05:06:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

arorarahul.0688
Posting Yak Master

India
125 Posts

Posted - 11/12/2007 :  07:09:43  Show Profile  Reply with Quote
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
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 11/12/2007 :  10:13:23  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 11/12/2007 :  13:44:22  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 11/12/2007 :  14:03:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

LoztInSpace
Aged Yak Warrior

939 Posts

Posted - 11/12/2007 :  17:14:27  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000