6

SQL Select a row for each category type

 3 years ago
source link: https://www.codesd.com/item/sql-select-a-row-for-each-category-type.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

SQL Select a row for each category type

advertisements

I've the data in my table in following format.

id | person_id| category | value| updated_date
-----------------------------
 1 | p1       |race  | r1 | 2015-02-26
 2 | p1       |race  | r2 | 2015-02-26
 3 | p2       |race  | r3 | 2015-02-27
 4 | p2       | race | r1 | 2015-02-28
 5 | p1       | lang | l1 | 2015-02-26

Now, I'm filtering based on the person's id. I need following in the result set.

  1. I need a record for each category that exists for this person.
  2. If there is more than one record for a category, list the once that is updated last.
  3. If the updated dates are same, get the first one in the list.

For p1,

1 | p1       |race  | r1 | 2015-02-26
5 | p1       | lang | l1 | 2015-02-26

It's something like this. because id is not a group by field because it changes on every row you select the min(id) for the group by personid,category, value.

select min(id), personid, category, value, max(updated_date)
group by personid, category, value
having Max(updated_date)     

/* min(id) is just to return the value of id
for the row having max(update_date)
having max(update_date) get's the highest date*/

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK