[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 347

SELECT first distinct record

I've got a query like this:

SET DATEFIRST 7
SELECT
*, DATEDIFF(d, '20070802', KLklacalendarmodels.validfrom) AS antal
FROM KLklacalendarmodels
WHERE
KLklacalendarmodels.location = 238 AND
DATEDIFF(d, '20070802', KLklacalendarmodels.validfrom) <= 0  AND
KLklacalendarmodels.day = Datepart(dw, '20070802')
ORDER BY antal DESC

And it returns something like this:

1605      238      8               5      100      2007-07-26 00:00:00.000      -7
1607      238      10              5      0      2007-07-26 00:00:00.000      -7
1003      238      10              5      100      2007-07-12 00:00:00.000      -21

What I want is it to return this:

1605      238      8               5      100      2007-07-26 00:00:00.000      -7
1607      238      10              5      0      2007-07-26 00:00:00.000      -7

The logic is that only one record pr FIELD3 is returned, and it should be the record with the highest FIELD7, i.e. the last row is not returned because it has the lowest value (-21) for all records where FIELD3 = 10.
0
speder
• 3
• 2
1 Solution

Commented:

a) what the column names in the table are
b) which columnn(s) comprise the primary key for the table, or at least which column(s) can serve as a unique identifier for the table.
0

Author Commented:
C1          C2       C3           C4      C5                         C6                         C7
1605      238      8               5      100      2007-07-26 00:00:00.000      -7
1607      238      10              5      0      2007-07-26 00:00:00.000      -7
1003      238      10              5      100      2007-07-12 00:00:00.000      -21

C1 is a unique identifier
0

Commented:
And you want rows that for distinct combinations of C2 and C3 or C2, C3 and C4?
0

Author Commented:
C1          C2       C3           C4      C5                         C6                         C7
1605      238      8               5      100      2007-07-26 00:00:00.000      -7
1607      238      10              5      0      2007-07-26 00:00:00.000      -7
1003      238      10              5      100      2007-07-12 00:00:00.000      -21

C1 is a unique identifier

What I want is a query that returns this:

C1          C2       C3           C4      C5                         C6                         C7
1605      238      8               5      100      2007-07-26 00:00:00.000      -7
1607      238      10              5      0      2007-07-26 00:00:00.000      -7

The logic is that only one row for each value in C3 is returned, and it should be the row with the lowest value in C7. In the above example the row with C1 = 1003 is removed because it has the value -21 in C7 (compared to -7 for C1 = 1607).

0

Author Commented:
Well, I guess I solved it myself by making a join with MAX(C7) - something like this:

SELECT
*, DATEDIFF(d, '20070802', KLklacalendarmodels.validfrom) AS antal
FROM KLklacalendarmodels a

INNER JOIN (SELECT MAX(C7)....) AS b ON a.xxx = b.xxx

WHERE
KLklacalendarmodels.location = 238 AND
DATEDIFF(d, '20070802', KLklacalendarmodels.validfrom) <= 0  AND
KLklacalendarmodels.day = Datepart(dw, '20070802')
ORDER BY antal DESC
0

Commented:
PAQed with points refunded (250)

Computer101