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

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

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
Asked:
speder
  • 3
  • 2
1 Solution
 
lahousdenCommented:
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.
0
 
spederAuthor 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
 
lahousdenCommented:
And you want rows that for distinct combinations of C2 and C3 or C2, C3 and C4?
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
spederAuthor 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
 
spederAuthor 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
 
Computer101Commented:
PAQed with points refunded (250)

Computer101
EE Admin
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now