Need SQL Query to fetch data from a table for MYSQL

Question

I have a table where there are columns like ID, USER, PreviousValue, CurrentValue, Date.

Need a query that will give the latest current value and the rest columns could be of any user based on user queried for.

Example: If the table has last entry for user A, and query is for User B, The ID, User, previous Value, Date should be returned for user B but the current Value should be for user A.

ID   Previous  Current  User     createdOn
1    RED       BLUE     System   14-MAR-2020
2    GREEN     YELLOW   ADMIN    12-MAR-2020 

IF I query for ADMIN as user the row returned should contain  below data:

ID   Previous  Current  User     createdOn
2    GREEN     BLUE     ADMIN    12-MAR-2020

As the latest row was added on 14 MARCH the current value should come from that row, rest all data should be of the user I queried for.

I have already provided the column names, do you need example data as well? — Shubham Parmar
– Shubham Parmar, Commented Apr 20, 2020 at 5:38
With example data and example output would be nice.. and please post as text and not image, thanks — FanoFN
– FanoFN, Commented Apr 20, 2020 at 5:40
I have already provided the column names Provide CREATE TABLE script, add INSERT INTO script with some sample data and desired result for this data. — Akina
– Akina, Commented Apr 20, 2020 at 5:54

Will B. · Accepted Answer · 2020-04-20 07:02:36Z

WARNING! Date Format Problem

I strongly urge you to convert the CreadedOn column to a DATE data-type, instead of VARCHAR, in order to retrieve the appropriate ordering of values by date.
Otherwise 14-MAR-2020 will be considered later than 11-DEC-2020.

Issue Example DB-Fiddle

Schema

CREATE TABLE users (
  `ID` INTEGER,
  `Previous` VARCHAR(5),
  `Current` VARCHAR(6),
  `User` VARCHAR(18),
  `createdOn` VARCHAR(20)
);


INSERT INTO users
  (`ID`, `Previous`, `Current`, `User`, `createdOn`)
VALUES
  ('1', 'RED', 'BLUE', 'System', '14-MAR-2020'),
  ('2', 'GREEN', 'YELLOW', 'ADMIN', '12-MAR-2020'),
  ('3', 'GREEN', 'PURPLE', 'System', '11-DEC-2020'),
  ('4', 'GREEN', 'YELLOW', 'System', '10-MAR-2020');

Other answer query https://stackoverflow.com/a/61316029/1144627

select
  Id,
  Previous,
  User,
  CreatedOn,
  (
    select Current
    from users
    order by CreatedOn desc
    limit 1
   ) as Current
from users
where `user` = 'ADMIN'
order by createdon desc
limit 1;

| Id  | Previous | User  | CreatedOn   | Current |
| --- | -------- | ----- | ----------- | ------- |
| 2   | GREEN    | ADMIN | 12-MAR-2020 | BLUE    |

Expected Current of PURPLE

To fix the issue with the date sorting, you will need to modify your table using the STR_TO_DATE() function.

It is important to note that comparing with STR_TO_DATE in your query instead of updating the column will cause a full-table scan, comparing every record in the table.

Example DB-Fiddle

ALTER TABLE users
ADD COLUMN createdOn_Date DATE NULL DEFAULT NULL;

UPDATE users
SET CreatedOn_Date = STR_TO_DATE(CreatedOn, '%d-%b-%Y')
WHERE CreatedOn_Date IS NULL;


ALTER TABLE users
DROP COLUMN CreatedOn,
CHANGE COLUMN CreatedOn_Date CreatedOn DATE;

Then display your records in your desired format, use the DATE_FORMAT() function

Other Answer Query https://stackoverflow.com/a/61316029/1144627

select
  Id,
  Previous,
  User,
  DATE_FORMAT(CreatedOn, '%d-%b-%Y') AS CreatedOn,
  (
    select Current
    from users
    order by CreatedOn desc
    limit 1
  ) as Current
from users
where `user` = 'ADMIN'
order by createdon desc
limit 1;

Result

| Id  | Previous | User  | CreatedOn   | Current |
| --- | -------- | ----- | ----------- | ------- |
| 2   | GREEN    | ADMIN | 12-Mar-2020 | PURPLE  |

Schwern · Accepted Answer · 2020-04-20 06:02:57Z

0

A subquery to get the latest current value should do it.

select
  Id,
  Previous,
  User,
  CreatedOn,
  (
    select Current
    from users
    order by CreatedOn desc
    limit 1
   ) as Current
from users
where user = ?
order by createdon desc
limit 1

answered Apr 20, 2020 at 6:02

Schwern

167k28 gold badges226 silver badges371 bronze badges

1 Comment

Will B. Over a year ago

@ShubhamParmar please be aware, you appear to have an issue with your CreatedOn column that may result in the incorrect values being retrieved by this answer, I provided a fix in my answer.

Collectives™ on Stack Overflow

Need SQL Query to fetch data from a table for MYSQL

2 Answers 2

WARNING! Date Format Problem

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

WARNING! Date Format Problem

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related