SQL Select and Sort from Multiple tables

Question

I have three tables:

Table Clothes

|id  | productname |   manufacturer  | arrivaldate  |
+----+-------------+-----------------+--------------+
|  1 |   Shirt     |   MfrA          |   5/30/20    |
|  2 |   Tshirt    |   MfrB          |   3/17/20    |

Table Toys

|id  | productname |   manufacturer   | arrivaldate  |
+----+-------------+------------------+--------------+
|  1 |    Car      |   MfrC           |   2/16/19    |
|  2 |    Ball     |   MfrD           |   8/25/20    |

Table Tools

|id  | productname |   manufacturer   | arrivaldate  |
+----+-------------+------------------+--------------+
|  1 |    Hammer   |   MfrE           |   4/17/20    |
|  2 |    Drill    |   MfrF           |   9/12/21    |

I would like to display 5 latest/newest products, included from each table, sorted by arrivaldate DESC. It can be a new DB View or Table.

Desired output would be like this:

Latest products arrival

| productname |   manufacturer   | arrivaldate  |
+-------------+------------------+--------------+
|    Drill    |   MfrF           |   9/12/21    |
|    Ball     |   MfrD           |   8/25/20    |
|    Shirt    |   MfrA           |   5/30/20    |

What would be SQL query for this?

You describe you want the 5 last products, but your expected result shows only three. Please elaborate. — The Impaler
– The Impaler, Commented Apr 17, 2022 at 23:14
Please post your current attempted query and clarify the arrivaldate format - the standard MySQL date is YYYY-MM-DD but yours is MM/DD/YY. It might help a lot if you post the results of SHOW CREATE TABLE table_name of all the tables in question. — FanoFN
– FanoFN, Commented Apr 17, 2022 at 23:34

blabla_bingo · Accepted Answer · 2022-04-18 02:29:39Z

5

If by display 5 latest/newest products, included from each table you mean 5 latest from the combined result set, a view using a UNION will do the job:

create view testvw as select * from 
(
(select productname,manufacturer,arrivaldate from Clothes)
UNION
(select productname,manufacturer,arrivaldate from Toys)
UNION
(select productname,manufacturer,arrivaldate from Tools)
) x 
order by arrivaldate desc limit 5;

answered Apr 18, 2022 at 2:29

blabla_bingo

2,2171 gold badge4 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DRapp · Accepted Answer · 2022-04-18 01:09:19Z

Actually, another person had an answer and deleted it which is what I would have also offered. The only difference is that SQL-Server allows the "TOP" keyword and MySQL uses "LIMIT".

By doing each table individually, you would order by the arrival date in DESCENDING order, thus floating the most recent entries to the top of the list. THEN, applying the LIMIT 5 will limit the results to only returning the first 5 AFTER the sorted results.

By doing a UNION of each table source (clothes, toys, tools), the concept is the same. I just tacked on a free-form description, so you know from which table it originated.

The UNION just requires that each table result has the same columns and number of columns. In this case, they are all the same and same name too.

select * from
   ( select
          'Clothes' as TableSource,
          id,
          productname,
          manufacturer,
          arrivaldate
       from
          Clothes
       order by
          arrivaldate desc
       limit 5 ) QryClothes    
UNION
select * from
   ( select
          'Toys' as TableSource,
          id,
          productname,
          manufacturer,
          arrivaldate
       from
          Toys
       order by
          arrivaldate desc
       limit 5 ) QryToys
UNION
select * from
   ( select
          'Tools' as TableSource,
          id,
          productname,
          manufacturer,
          arrivaldate
       from
          Tools
       order by
          arrivaldate desc
       limit 5 ) QryTools

Collectives™ on Stack Overflow

SQL Select and Sort from Multiple tables

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related