Home select only top record
Reply: 2

select only top record

nur wahidah
1#
nur wahidah Published in 2018-02-14 07:08:20Z

I have SQL like this

SELECT manjalink_id, printed_serial, full_name, vm.cu_id
from [dbfastsconfigdataref].[dbo].[v_manja_user_cuid] vm
INNER JOIN dbmanjaprofile.dbo.m_manja_user m on m.manjalink_id = 
vm.manja_user_id
INNER JOIN dbfastsconfigdataref.dbo.cdf_micard mi on mi.cu_id = vm.cu_id
where m.created_timestamp >= '2018-02-01'
order by m.created_timestamp, full_name

with output

    Manjalink_id       printed_serial     full_name      cu_id
 ------------------------------------------------------------------
        101              00000123          alex          5556566
        102              00000124          alex          5556899
        103              00000126          max           8989899

and I would like to be like this:

 Manjalink_id       printed_serial     full_name      cu_id
 ------------------------------------------------------------------
        101              00000123          alex          5556566
        103              00000126          max           8989899

How to get the result as the desired output?

Thanks!

Mahesh.K
2#
Mahesh.K Reply to 2018-02-14 07:20:45Z

Try Something like this .

Use Row_number() to generate a sequence number based on your columns and pick the required Sequence values .

select manjalink_id, printed_serial, full_name, cu_id  from (
SELECT manjalink_id, printed_serial, full_name, vm.cu_id ,ROW_NUMBER()over(partition by full_name order by manjalink_id) Rnum
from [dbfastsconfigdataref].[dbo].[v_manja_user_cuid] vm
INNER JOIN dbmanjaprofile.dbo.m_manja_user m on m.manjalink_id = 
vm.manja_user_id
INNER JOIN dbfastsconfigdataref.dbo.cdf_micard mi on mi.cu_id = vm.cu_id
where m.created_timestamp >= '2018-02-01'
)as a
where Rnum=1

Note : For your reference check Row_number() .And also there are lot many websites to learn about it

Anagha
3#
Anagha Reply to 2018-02-14 08:40:04Z
SELECT manjalink_id, printed_serial, full_name, cu_id
from
(SELECT manjalink_id, printed_serial, full_name, vm.cu_id, row_number() over (partition by full_name order by manjalink_id) rn
from [dbfastsconfigdataref].[dbo].[v_manja_user_cuid] vm
INNER JOIN dbmanjaprofile.dbo.m_manja_user m on m.manjalink_id = 
vm.manja_user_id
INNER JOIN dbfastsconfigdataref.dbo.cdf_micard mi on mi.cu_id = vm.cu_id
where m.created_timestamp >= '2018-02-01'
) a where rn = 1
order by full_name 
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.306261 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO