Hi, i have a sql server data table which contains media information of songs like mediaID, mediaName, albumID, singerID and genre. can someone help me write a sql query getting the last song of each of the last 4 albums (the last song of each album) and
secondly, the last song of each of the 4 last entered genre. the album names and data are in different table and connected to the media table by the id. so, what i need to do is some thing like- get the 4 last albumID, and give me one song data for each of
them.
i tried to use "distinct", but i can't get all the table information by using it.
--this will give you last 4 entered album details with singname
SELECT DISTINCT TOP 4 albums_table.ID,albumname, albumprice, singer,singName,
FROM albums_table
INNER JOIN madia_table ON albums_table.ID=madia_table.albumID
WHERE singName='summer' -- you can pass where condition here
ORDER BY createDate DESC
select t1.*, t2.*
from albums_table t1
inner join madia_table t2 on t1.ID = t2.albumID
inner join (
select top 4 albumID from madia_table order by createDate desc
) t3 on t1.ID = t3.albumID
I think this is what you want: The last 4 albums, plus the last song on each. Since you don't mention a date field in the album table, I am using ID:
SELECT TOP 4 *
FROM albums_table a
INNER JOIN madia_table m ON m.ID=(
SELECT TOP 1 m2.ID
FROM madia_table m2
WHERE m2.albumID=a.ID
ORDER BY createDate DESC
)
ORDER BY a.ID DESC
-Tab Alleman
Marked as answer by Chen Yu - MSFT on May 14, 2012 11:23 AM
Did you get the right query? From your table structures, did you have any order when you insert data into madia_ table? Order by ID or order by createDate? If you didn't get the right query, please provide us some sample data, it will give us more inforation
about your problem.
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
yanivhanya
Participant
1155 Points
323 Posts
select distinct from multipale tables
May 01, 2012 06:49 AM|LINK
Hi, i have a sql server data table which contains media information of songs like mediaID, mediaName, albumID, singerID and genre. can someone help me write a sql query getting the last song of each of the last 4 albums (the last song of each album) and secondly, the last song of each of the 4 last entered genre. the album names and data are in different table and connected to the media table by the id. so, what i need to do is some thing like- get the 4 last albumID, and give me one song data for each of them.
i tried to use "distinct", but i can't get all the table information by using it.
thanks...
yrb.yogi
Star
14460 Points
2402 Posts
Re: select distinct from multipale tables
May 01, 2012 07:24 AM|LINK
DISTINCT is working on select column list.
You must have to select only those columns which are giving you distinct records.
You can also use the rank/row number function to get the unique values.
Post your other tables structure with data & expected output
.Net All About
yanivhanya
Participant
1155 Points
323 Posts
Re: select distinct from multipale tables
May 01, 2012 07:45 AM|LINK
i know you can't other columns, this is my problem.
let's say-
albums_table-
ID, albumname, albumprice, singer
madia_table
ID, albumID, singName, singPrice, createDate
now, what i need "get all the data of each song from the last 4 albums... so if i entered new albums "summer", "winter",
i need to get the last sing in "summer" and all the data (album data and sing data)
and the last sing in "winter" and all the data
yrb.yogi
Star
14460 Points
2402 Posts
Re: select distinct from multipale tables
May 01, 2012 08:21 AM|LINK
.Net All About
sandeepmitta...
Contributor
6957 Points
1082 Posts
Re: select distinct from multipale tables
May 01, 2012 10:21 AM|LINK
check if you are looking for this
Sandeep Mittal | My Blog - IT Developer Zone
limno
All-Star
117430 Points
8032 Posts
Moderator
MVP
Re: select distinct from multipale tables
May 01, 2012 12:57 PM|LINK
You can use a ranking function like row_number to get your result. Post some sample data along with your table structures and ecpexcted result.
Thanks.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
TabAlleman
All-Star
15741 Points
2724 Posts
Re: select distinct from multipale tables
May 01, 2012 01:24 PM|LINK
I think this is what you want: The last 4 albums, plus the last song on each. Since you don't mention a date field in the album table, I am using ID:
Chen Yu - MS...
All-Star
21829 Points
2513 Posts
Microsoft
Re: select distinct from multipale tables
May 07, 2012 07:58 AM|LINK
Hi yanivhanya,
Did you get the right query? From your table structures, did you have any order when you insert data into madia_ table? Order by ID or order by createDate? If you didn't get the right query, please provide us some sample data, it will give us more inforation about your problem.
Thanks.
Feedback to us
Develop and promote your apps in Windows Store