select distinct from multipale tables

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...

Re: select distinct from multipale tables

yanivhanya
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

Re: select distinct from multipale tables

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

Re: select distinct from multipale tables

--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

Re: select distinct from multipale tables

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

Re: select distinct from multipale tables

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.

Re: select distinct from multipale tables

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

Re: select distinct from multipale tables

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.