[Show all top banners]

phone
Replies to this thread:

More by phone
What people are reading
Subscribers
:: Subscribe
Back to: Kurakani General Discussion Refresh page to view new replies
 Please help on Sql query
[VIEWED 1345 TIMES]
SAVE! for ease of future access.
Posted on 07-03-19 5:11 PM     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

​Hello , I have a table called Student consisting of Columns Name, and DateOfBirth. I would like to create a query Which Selects all Name whose DateOfBirth is on the same day. The DateOfBirth Column datatype is in DateTime format. I want the result in the Date format. In my table below I want row 1,2, and 7 for day 1 and 3,4 for day 2 as a result of the query.


 
Posted on 07-03-19 6:36 PM     [Snapshot: 49]     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

Use "GROUP BY" on DOB and get list of names in one column by using STRING AGG functions.
 
Posted on 07-03-19 6:38 PM     [Snapshot: 52]     Reply [Subscribe]
Login in to Rate this Post:     1       ?         Liked by
 

select DATE_OF_BIRTH, LISTAGG(NAME, ',') WITHIN GROUP (ORDER BY NAME) AS NAME
from TABLE
GROUP BY DATE_OF_BIRTH;

Good Luck !!!
 
Posted on 07-04-19 10:17 AM     [Snapshot: 162]     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

Hello basnyatt,

When I run the query it throws the error saying:
"The function 'ListAgg' may not have a WITHIN GROUP clause."
 
Posted on 07-05-19 10:21 AM     [Snapshot: 259]     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

what RDBMS are you using
 
Posted on 07-05-19 12:18 PM     [Snapshot: 312]     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

select x.*, rownum as day_num from (
select to_date((to_char(dateOfBirth, 'YYYY-MM-DD')),'YYYY-MM-DD') date_of_Birth, listagg(Name, ',') within group(order by Name) Name_of_students
from student
group by to_date((to_char(dateOfBirth, 'YYYY-MM-DD')),'YYYY-MM-DD')
) x;


this works for Oracle. If you are using mysql try using string_agg instead of listagg.
 
Posted on 07-06-19 1:21 PM     [Snapshot: 410]     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

Hello basnyatt,

I am using Microsoft SQL Server 2012.
 
Posted on 07-07-19 11:37 AM     [Snapshot: 489]     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

Hello raajkm,

I need the skript for Microsoft SQL Server. Please help me.


Last edited: 08-Jul-19 08:07 AM

 
Posted on 07-08-19 12:14 PM     [Snapshot: 642]     Reply [Subscribe]
Login in to Rate this Post:     0       ?        
 

WELL i do not use sql server 2012 but you might use the query as;

select to_date((to_char(S1.dateOfBirth, 'YYYY-MM-DD')),'YYYY-MM-DD') date_of_Birth,
stuff ((select distinct ','+ Name from student s2 where s2.name=s1.name FOR XML PATH(' ')),1,1,' ') as name_of_students

from student s1 group by to_date((to_char(S1.dateOfBirth, 'YYYY-MM-DD')),'YYYY-MM-DD')
Last edited: 08-Jul-19 12:16 PM
Last edited: 08-Jul-19 12:17 PM

 


Please Log in! to be able to reply! If you don't have a login, please register here.

YOU CAN ALSO



IN ORDER TO POST!




Within last 365 days
Recommended Popular Threads Controvertial Threads
TRUMP 2016!!! Here is why?
Returning to Nepal
महानियिका प्रियंका कार्कीजीलाइ खुल्ला पत्र !
भाउजु आउनु भाकी छिन रे नि !
~चौतारी १९९~
तुरन्त सम्पर्क गर्नु होस् !
पिन्कुको नया काले हस्पि नाक चुचेय भाते भतुवा
Mnang, Mustang Treking and Our Engagement
Tired of nepali attitude
👭 👭 👭 👭 देसी भाभी 👭 👭 👭 👭
Indian Worker Attitude
Wedding Costs in Nepal ? Party, Band Baaja !!!!???
Learning computer programming
प्रियंका कार्की फोटो अबलम भाग २ , ३ जून २०१६ copyright @पुन्टर
वाह ! मन्त्री ज्यु वाह !
प्रियंका .का. लै चिठी भाग १
Four Characters and My Funeral
ICE RAID
सेतो इन्द्रणी
"टी पी एस" अत्यन्त जरुरी
Don't apply Canada Immigration through COLIN R. SINGER
Former U.S. President Bill Clinton in Nepal
Build the Wall-MAGA
Finally tps extended (18 months) ( full statement)
Why you can't trust the vaccines 100%
~चौतारी १९९~
महानियिका प्रियंका कार्कीजीलाइ खुल्ला पत्र !
TRUMP 2016!!! Here is why?
NOTE: The opinions here represent the opinions of the individual posters, and not of Sajha.com. It is not possible for sajha.com to monitor all the postings, since sajha.com merely seeks to provide a cyber location for discussing ideas and concerns related to Nepal and the Nepalis. Please send an email to admin@sajha.com using a valid email address if you want any posting to be considered for deletion. Your request will be handled on a one to one basis. Sajha.com is a service please don't abuse it. - Thanks.

Sajha.com Privacy Policy

Like us in Facebook!

↑ Back to Top
free counters