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.
| Author |
Topic |
|
lambertal
Starting Member
3 Posts |
 Posted - 2002-10-09 : 01:38:09
|
| Grey matter over extended, please assist ancient oneTestTable:ColA ColB ColC ColD101 10101 nam11 it1 101 10102 nam12 it2 101 10103 nam13 1t3 102 10201 nam21 it21 102 10202 nam22 it22 102 10203 nam23 it23 103 10301 nam31 it31 103 10302 nam32 it32 103 10303 nam33 it33 Results needed: 101 10101 nam11 it1 102 10201 nam21 it21 103 10301 nam31 it31 For each DISTINCT ColA, I need the complete rowset....List of trial SQL's would choke the forum..........please??thanks, al |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-10-09 : 02:43:59
|
| How about this:select ColA, min(ColB), min(ColC), min(ColD)from TestTablegroup by ColA |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-10-09 : 10:31:56
|
More likelySELECT *FROM TestTable AS T1WHERE ColB = ( SELECT MIN(ColB) FROM TestTable AS T2 WHERE T1.ColA = T2.ColA ) |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-10-09 : 10:48:42
|
| Yeah maybe... But that can return more than one row per ColA. And in the case described here the statements would return the same data. Your choice, al!Edited by - andraax on 10/09/2002 10:49:49 |
|
|
|
lambertal
Starting Member
3 Posts |
Posted - 2002-10-10 : 11:28:56
|
| Thanks, from my testing in real data, both seem to be fine (many thanks). I would like to understand better your comment about returning more that one row per ColA.thanks, al. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-10-10 : 15:42:58
|
quote:
both seem to be fine
That surprises me: for most data only one (or neither!) of these solutions could be what you wanted.Andraax's reading of what you wanted and mine differed considerably.His solution returns one row for each distinct ColA value and the smallest value in ColB for any row with that ColA value. Similarly for ColC and ColD. In other word, the result will not be a selection of lines from TestTable, unless all the minimum ColB, ColC and ColD occur in the same row for each ColA value.I'd assumed that you wanted to select the rows from TestTable with the minimum value of ColB for each distinct ColA value. As Andraax says, this can result in multiple rows for a given ColA value: if there are multiple rows with the same ColA and minimum ColB then they will both match the subquery in the where clause. I'd rather assumed that (ColA, ColB) was a candidate key for TestTable, and so this wouldn't happen. |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-10-11 : 09:26:24
|
How about this to solve both problems (elaborating on Arnold's solution):[edit]Scratch that... Me=stupid  [/edit]Edited by - andraax on 10/11/2002 09:27:16 |
|
|
|
|
|
|
|
|
My DISTINCT stinx........