CODEWITHSUNDEEP
sqlmysql
user12345
user12345

Reputation: 2418

how to solve this particular query?

table1         table2 

col1 date     col1 date
a    d1       e     d4
b    d2       f     d5
c    d3       a     d6

I want a new table which have 4 entries order by date but if any entries of column 1 replicate it remove this duplication also. suppose if my date order is d1>d6>d2>d3>d5>d4 then result should be:

col1 date
a     d1
b     d2
c     d3
e     d5

And I also want to know which data is coming from tabl1 or table2.

Upvotes: 0

Views: 81

Answers (2)

DRapp
DRapp

Reputation: 48139

select 
      col1,
      min( date ) date
   from 
      ( select col1, date
           from table1
        union all
        select col1, date
           from table2 )
   limit 4
   group by
      1
   order by
      2

And from the data sample, I think you want 5 entries.. you missed element "f" from your data.

Upvotes: 0

Denis Valeev
Denis Valeev

Reputation: 6015

Try this:

select 
top 4 --you only need 4 of them?
col1, min(date) [date] from 
(
select col1, date from table1 
union 
select col1, date from table2
) t
group by col1
order by col1

Upvotes: 1

Related Questions