sql newbie question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql newbie question

Author

Topic

dardar
Starting Member

10 Posts

Posted - 2011-11-02 : 02:24:53

Hi guys (and girls...).
new to SQL and i need some help.

i got a table with 4 columns: ID(int), FILE_ID(varchar), FILE_NAME(varchar), IS_READ(bit).
i need to get all the records that IS_READ=0 , but if there are 2 (or more) records with the same FILE_NAME need to only get one of them.
i tried this:

select *
from TEST_TABLE
where IS_READ=0
group by FILE_NAME

but i got an exception:
Column 'test_table.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-02 : 02:35:12

what are the columns you need in your result ?

KH
[spoiler]Time is always against us[/spoiler]

shafi_sunshine
Starting Member

3 Posts

Posted - 2011-11-02 : 02:47:05

Hi,

Try This....

select distinct FILE_NAME,ID,FILE_ID,IS_READ from TEST_TABLE where IS_READ =0

Thanks
Shafi

dardar
Starting Member

10 Posts

Posted - 2011-11-02 : 02:50:52

quote:
Originally posted by khtan

what are the columns you need in your result ?

KH
[spoiler]Time is always against us[/spoiler]

all the record

dardar
Starting Member

10 Posts

Posted - 2011-11-02 : 02:53:34

quote:
Originally posted by shafi_sunshine

Hi,

Try This....

select distinct FILE_NAME,ID,FILE_ID,IS_READ from TEST_TABLE where IS_READ =0

Thanks
Shafi

no good. still get 2 records with the same file name

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-02 : 03:02:35

[code]
select *
from (
select *, rn = row_number() over ( partition by FILE_NAME order by FILE_NAME)
from TEST_TABLE
where IS_READ=0
) t
where t.rn = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dardar
Starting Member

10 Posts

Posted - 2011-11-02 : 04:03:44

quote:

Originally posted by khtan


select *
from (
        select *, rn = row_number() over ( partition by FILE_NAME order by FILE_NAME)
        from   TEST_TABLE
        where  IS_READ=0
     ) t
where t.rn = 1

KH
[spoiler]Time is always against us[/spoiler]

that did the work but added new column name rn to the result.
anyway i found a solution (but not a nice one)

select TEST_TABLE.*
from TEST_TABLE
where is_read=0
and id= (
select MAX(id)
from TEST_TABLE as t1
where t1.file_name = TEST_TABLE.file_name
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-02 : 04:10:03

quote:
that did the work but added new column name rn to the result.

You should always specify the column in the SELECT clause. For best practice, don't use SELECT * or TEST_TABLE.* . I do that in my query as i do not know what are the columns in your table and what are the columns that you required. The purpose of the query is to demonstrate the use of row_number() to obtain the result that you required.


select FILE_NAME,ID,FILE_ID,IS_READ
from (
        select *, rn = row_number() over ( partition by FILE_NAME order by FILE_NAME)
        from   TEST_TABLE
        where  IS_READ=0
     ) t
where t.rn = 1

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources