| Author |
 Topic  |
|
|
ncdesigner
Starting Member
1 Posts |
 Posted - 11/12/2007 : 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
Sweden
29138 Posts |
Posted - 11/12/2007 : 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" |
Edited by - SwePeso on 11/12/2007 03:33:25 |
 |
|
|
arorarahul.0688
Posting Yak Master
India
125 Posts |
Posted - 11/12/2007 : 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
Sweden
29138 Posts |
Posted - 11/12/2007 : 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
India
125 Posts |
Posted - 11/12/2007 : 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
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 11/12/2007 : 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
Flowing Fount of Yak Knowledge
3832 Posts |
Posted - 11/12/2007 : 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
Sweden
29138 Posts |
Posted - 11/12/2007 : 14:03:52
|
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" |
|
|
|
LoztInSpace
Aged Yak Warrior
876 Posts |
Posted - 11/12/2007 : 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. |
|
|
| |
Topic |
|
|
|
Help with a distinct query
