Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post May 07, 2012 07:58 AM by Chen Yu - MSFT
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
i tried to use "distinct", but i can't get all the table information by using it.
May 01, 2012 07:24 AM|LINK
tried to use "distinct", but i can't get all the table information by using it.
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
May 01, 2012 07:45 AM|LINK
i know you can't other columns, this is my problem.
ID, albumname, albumprice, singer
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
May 01, 2012 08:21 AM|LINK
--this will give you last 4 entered album details with singname
SELECT DISTINCT TOP 4 albums_table.ID,albumname, albumprice, singer,singName,
INNER JOIN madia_table ON albums_table.ID=madia_table.albumID
WHERE singName='summer' -- you can pass where condition here
ORDER BY createDate DESC
May 01, 2012 10:21 AM|LINK
check if you are looking for this
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
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.
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:
SELECT TOP 4 *
FROM albums_table a
INNER JOIN madia_table m ON m.ID=(
SELECT TOP 1 m2.ID
FROM madia_table m2
ORDER BY createDate DESC
ORDER BY a.ID DESC
May 07, 2012 07:58 AM|LINK
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.