SQL gets the last record
I am struggling to extract two results from this table. I have a table with different types, elements and values that are added on different dates.
Type Item Value Date
1 10 111 20160101
1 10 122 20170101
1 11 222 20170101
1 12 312 20170101
2 10 144 20160101
2 10 155 20170101
2 11 105 20160101
2 11 133 20170101
2 11 123 20180101
3 10 111 20170101
At first I would like only the most recent date, to show the combination of type, element. I tried this query but it will show me all dates.
SELECT Type, Item, Value, MAX(Date) as Date from TABLENAME
WHERE Type='1' or Type='2'
GROUP BY Type, Item, Value
Below is what I want it to look like.
Type Item Value Date
1 10 122 20170101
1 11 222 20170101
1 12 312 20170101
2 10 155 20170101
2 11 123 20180101
Then we have a more difficult question: here I am completely lost and not sure if this is possible, or maybe I should do it some other way? I would like to see if an element with type 1 has a value and type 2 does not. Or vice versa. Displayed in a new table.
Item Type1 Type2 Date
10 1 2 20170101 -> Item 10 has values with both type 1,2 on specific date
11 1 20170101 -> Item 11 has only a value when type 1 on specific date
11 2 20180101 -> Item 11 has only a value when type 2 on specific date
12 1 20170101 -> Item 12 has only a value when type 1 on specific date
+3
source to share
2 answers
For your first question, I think you can use:
SELECT A.TYPE, A.ITEM, A.VALUE, A.DATE
FROM TABLENAME A
INNER JOIN ON (SELECT TYPE, ITEM, MAX(DATE) AS MAXDATE
FROM TABLENAME
GROUP BY TYPE, ITEM) B ON A.TYPE = B.TYPE AND A.ITEM = B.ITEM AND A.DATE=B.MAXDATE
WHERE A.TYPE='1' OR A.TYPE='2'
Second, you can try this:
SELECT DISTINCT A.ITEM, B.TYPE AS TYPE1, C.TYPE AS TYPE2, CASE WHEN B.TYPE IS NOT NULL THEN B.DAT ELSE C.DAT END AS DATE_X
FROM
(SELECT ITEM, TYPE, MAX(DATE) AS DAT
FROM ITEM
GROUP BY ITEM, TYPE) A
LEFT JOIN (SELECT ITEM, TYPE, MAX(DATE) AS DAT FROM ITEM WHERE TYPE='1' GROUP BY ITEM, TYPE) B ON A.ITEM = B.ITEM AND A.DAT= B.DAT
LEFT JOIN (SELECT ITEM, TYPE, MAX(DATE) AS DAT FROM ITEM WHERE TYPE='2' GROUP BY ITEM, TYPE) C ON A.ITEM = C.ITEM AND A.DAT= C.DAT
Output:
ITEM TYPE1 TYPE2 DATE_X
1 10 1 2 20170101
2 11 NULL 2 20180101
3 11 1 NULL 20170101
4 12 1 NULL 20170101
+1
source to share