5

I have a table with 106 columns. One of those columns is a "Type" column with 16 types.

I want 16 rows, where the Type is distinct. So, row 1 has a type of "Construction", row 2 has a type of "Elevator PVT", etc.

Using Navicat.

From what I've found (and understood) so far, I can't use Distinct (because that looks across all rows), I can't use Group By (because that's for aggregating data, which I'm not looking to do), so I'm stuck.

Please be gentle- I'm really really new at this.

Below is a part of the table (how can I share this normally?)- it's really big so I didn't share the whole thing. Below is a partial result I'm looking for, where the Violation_Type is unique and the rest of the columns display.

Got it.. Sheesh... (took me forever, but got it...)

  D_ID   B_ID   V_ID       V_Type      S_ID   c_f   d_y    l_u    p_s   du_p  
 ------ ------ ------- -------------- ------ ----- ------ ------ ----- ------ 
   184    117   V 032   Elevator PVT      2     8      0      0               
     4    140   V 100   Construction      1     8      0      0               
    10    116   V 122   Electric                1      8   2005     0      0  
    11    117   V 033   Boiler Local      1     0   2005      0     0       
Avi
  • 53
  • 7
  • 2
    Please post sample data and expected result. – Felix Pamittan Sep 08 '15 at 00:29
  • 1
    @Avi, please edit the post and put it in there – Rohit Gupta Sep 08 '15 at 00:38
  • @RohitGupta Will do, thanks! – Avi Sep 08 '15 at 00:40
  • @Avi, instead of putting the data in the comment section, kindly edit your question and put it in there. Please apply necessary formatting too. – Felix Pamittan Sep 08 '15 at 00:42
  • @RohitGupta Any better? Or should I modify the column names to make it easier to read? – Avi Sep 08 '15 at 00:44
  • Better, I ahve marked up the data a bit better for you, but it wont show up for a while. JUst so you know how to do it next time. And I have upvoted it. – Rohit Gupta Sep 08 '15 at 00:47
  • Thanks! I appreciate it! – Avi Sep 08 '15 at 00:52
  • @Avi, still not clear. Can you fix the markup? – Felix Pamittan Sep 08 '15 at 00:56
  • Is this for MySQL or for Microsoft's SQL Server? Those are completely different databases with different dialects of SQL so the answers will be completely different. Please edit your question with the correct RDBMS. – Bacon Bits Sep 08 '15 at 01:36
  • You say you only want 16 rows and one row for each `V_Type`. Exactly what are you expecting to be returned in the other 105 columns? Which row should be used for the data to return if you only want one? The database will not guess. – Bacon Bits Sep 08 '15 at 01:38
  • @BaconBits Information from the rest of the row where the "V_Type" is coming from. It could be "top" (if that makes sense?) or any other way where I can arbitrarily select a row (I could technically use Building_ID and create 16 Violations under a single building). – Avi Sep 08 '15 at 02:03
  • But *which row*? Presumably there's multiple records with the same `V_type`. Say that there are 10 rows with `V_type = 'Elevator PVT'`. Which row of those 10 should be returned? – Bacon Bits Sep 08 '15 at 02:09
  • @BaconBits The first one? I don't know. I wrote my first query last week- I'm getting better, but I'm really not an expert at this just yet. I don't claim to know how it all works. – Avi Sep 08 '15 at 02:13

1 Answers1

3

You can use ROW_NUMBER for this:

SELECT *
FROM(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY V_Type ORDER BY (SELECT NULL))
    FROM tbl
)t
WHERE rn = 1

Modify the ORDER BY depending on what row you want to prioritize.


From the documentation:

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

This means that for every row within a partition (specified by the PARTITION BY clause), sql-server assigns a number from 1 depending on the order specified in the ORDER BY clause.

ROW_NUMBER requires an ORDER BY clause. SELECT NULL tells the sql-server that we do not want to enforce a particular order. We just want the rows numbered by partition.

The WHERE rn = 1 obviously filters only rows that has a ROW_NUMBER of 1. This gives you one row for every V_TYPE available.

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • Would it be too much trouble for you to explain what's going on and how it's working please? Thanks for the answer! It's awesome! – Avi Sep 08 '15 at 02:15