SQL Query to select random value of a common data

Question

I want an SQL statement to get the row with a minimum value to the "C4".

Consider the table:

I need something like this

I need to group N2 and N3 because the value of C2 and C3 is the same, I can select any value of C4 to group, in this case we can take 10 or 20, or in C1 with same N4 and N5. Can take 12 or 14.

Not need the maximum to grout, I need one value with C2 and C3 are the same. C1 is a Id this can be 1,2,3.. etc

Thanks for any help!

how can the minimum value for C4 be 30,10, 12 your question makes no sense — Bryan Dellinger, Jan 23 '17 at 23:11
http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — david25272, Jan 23 '17 at 23:34
http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column — david25272, Jan 23 '17 at 23:34

score 1 · Answer 1 · answered Jan 23 '17 at 23:26

1

SELECT * FROM yourtable
WHERE C4 = (SELECT MIN(C4) FROM yourtable)

answered Jan 23 '17 at 23:26

dadde

649
1
11
24

score 1 · Accepted Answer · answered Jan 23 '17 at 23:36

1

Can't exactly match your question with the expected answer as shown.

I suppose you're trying to do aggregation with grouping on C2 / C3 columns here. You may try this.

SELECT * 
FROM [Table_Name] t
    JOIN
        (
            SELECT 
                C2, C3, MIN(C4) as MIN_C4
            FROM 
                [Table_Name]
            GROUP BY C2, C3
        ) Aggreated_Table ON Aggreated_Table.C2 = t.C2 AND Aggreated_Table.C3 = t.C3 AND Aggreated_Table.MIN_C4 = t.C4

Alternatively, using ROW_NUMBER()

SELECT C1, C2, C3, C4
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY C2, C3 ORDER BY C4) AS rk FROM [TABLE_NAME]
) Ranked_Table
WHERE rk = 1

answered Jan 23 '17 at 23:36

ydoow

2,969
4
24
40

This is tagged Oracle. [Table_Name] is invalid syntax. – MT0 Jan 24 '17 at 01:38
That's a tag suggesting a replacement with original table name.... – ydoow Jan 24 '17 at 01:46
-i can solve this with the ROW_NUMBER() thx – Wzap PepexD Feb 01 '17 at 01:51

SQL Query to select random value of a common data

2 Answers2