CODEWITHSUNDEEP
sqlt-sql
user1714470
user1714470

Reputation: 11

Selecting and sorting data from a single table

Correction to my question....

I'm trying to select and sort in a query from a single table. The primary key for the table is a combination of a serialized number and a time/date stamp.

The table's name in the database is "A12", the columns are defined as:

Serial2D (PK, char(25), not null)
Completed (PK, datetime, not null)
Result (smallint, null)
MachineID (FK, smallint, null)
PT_1 (float, null)
PT_2 (float, null)
PT_3 (float, null)
PT_4 (float, null)

Since the primary key for the table is a combination of the "Serial2D" and "Completed", there can be multiple "Serial2D" entries with different values in the "Completed" and "Result" columns. (I did not make this database... I have to work with what I got)

I want to write a query that will utilize the value of the "Result" column ( always a "0" or "1") and retrive only unique rows for each "Serial2D" value. If the "Result" column has a "1" for that row, I want to choose it over any entries with that Serial that has a "0" in the Result column. There should be only one entry in the table that has a Result column entry of "1" for any Serial2D value.

Ex. table

   Serial2d  Completed  Result   PT_1   PT_2    PT_3   PT_4   
   -------   -------    ------   ----   ----   ----   ----
    A1       1:00AM      0       32.5    20     26     29
    A1       1:02AM      0       32.5    10     29     40
    A1       1:03AM      1       10      5       4      3
    B1       1:04AM      0       29      4       1      9
    B1       1:05AM      0       40      3       4      9
    C1       1:06AM      1       9       7       6      4

I would like to be able to retrieve would be:

   Serial2d  Completed  Result   PT_1   PT_2    PT_3   PT_4   
   -------   -------    ------   ----   ----   ----   ----
    A1       1:03AM      1       10      5       4      3
    B1       1:05AM      0       40      3       4      9
    C1       1:06AM      1       9       7       6      4

I'm new to SQL and I'm still learning ALL the syntax. I'm finding it difficult to search for the correct operators to use since I'm not sure what I need, so please forgive my ignorance. A post with my answer could be staring me right in the face and i wouldn't know it, please just point me to it.

I appreciate the answers to my previous post, but the answers weren't sufficient for me due to MY lack of information and ineptness with SQL. I know this is probably insanely easy for some, but try to remember when you first started SQL... that's where I'm at.

Upvotes: 1

Views: 124

Answers (4)

Taryn
Taryn

Reputation: 247810

Since you are using SQL Server, you can use Windowing Functions to get this data.

Using a sub-query:

select *
from 
(
  select *,
    row_number() over(partition by serial2d 
                      order by result desc, completed desc) rn
  from a12
) x
where rn = 1

See SQL Fiddle with Demo

Or you can use CTE for this query:

;with cte as
(
  select *,
    row_number() over(partition by serial2d 
                      order by result desc, completed desc) rn
  from a12
)
select *
from cte c
where rn = 1;

See SQL Fiddle With Demo

Upvotes: 1

acatt
acatt

Reputation: 487

This can be solved using a correlated sub-query:

SELECT 
    T.serial, 
    T.[time], 
    0 AS result
FROM tablename T
WHERE 
    T.result = 1
  OR 
    NOT EXISTS(
        SELECT 1
        FROM tablename
        WHERE 
            serial = T.serial
          AND (
               [time] > T.[time]
             OR 
               result = 1 
          )
    )

Upvotes: 0

Beth
Beth

Reputation: 9617

SELECT 
  t.Serial,
  max_Result, 
  MAX([time]) AS max_time
FROM 
  myTable t inner join
  (SELECT 
    Serial, 
    MAX([Result]) AS max_Result
   FROM 
    myTable
   GROUP BY 
    Serial) m on
   t.serial = m.serial and
   t.result = m.max_result
group by
 t.serial,
 max_Result

Upvotes: 0

Kermit
Kermit

Reputation: 34063

You can group by Serial to get the MAX of each Time.

SELECT Serial, MAX([Time]) AS [Time]
FROM myTable
GROUP BY Serial
HAVING MAX(Result) => 0

Upvotes: 0

Related Questions