asked on

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.

lahousden

Please tell us:

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.

speder

ASKER

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

lahousden

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

speder

ASKER

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).

speder

ASKER

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

ASKER CERTIFIED SOLUTION

Computer101

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial