help with an sql problem

Question

help with an sql problem

dardar4

SSC-Addicted

Points: 403
More actions
August 17, 2011 at 2:43 am

#255674

hi all
i have a table with those columns: index[int], file_name[nvarchar(50)], is_modified [bit]
i need to write an sql statement that will do the following:
go over all the records in the table and return only those that is_modified=0(i.e false) and if 2 records have the same file name return the one with the larger index value.
thanks guys!

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

dardar4 (8/17/2011)
hi all
i have a table with those columns: index[int], file_name[nvarchar(50)], is_modified [bit]
i need to write an sql statement that will do the following:
go over all the records in the table and return only those that is_modified=0(i.e false) and if 2 records have the same file name return the one with the larger index value.
thanks guys!

This is very simple to do. How about setting up a little sample data to test the code against? One of the links in my sig will show you how to do it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dardar4 SSC-Addicted Points: 403 More actions · Answer 2

This is very simple to do. How about setting up a little sample data to test the code against? One of the links in my sig will show you how to do it.

not sure what u want me to do......

if i would know the sql statement i could have test it, but since I'm not sure what to write i don't have a way to test it.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Like this:

CREATE TABLE #Sample (some_index INT, [file_name] nvarchar(50), is_modified bit)

INSERT INTO #Sample (some_index, [file_name], is_modified)

SELECT 1, 'anything.txt', 0

but with a few more rows.

Don't name a column [index], or any other TSQL keyword. It's confusing and may have unexpected consequences.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Sounds like a homework question to me. Can you show us what you've tried so far?

John

dardar4 SSC-Addicted Points: 403 More actions · Answer 5

CREATE TABLE [dbo].[MyTable]

(

rec_index INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

file_desc nvarchar(50),

is_modified bit

)

this is my table.

now i need an sql statement that will do what i noted before.

the object is to show all the records that are not modified. that's easy

select * from MyTable where is_modified = 0

the problem is i don't know how to add another condition:

if there are 2 or more records with the same file_desc - show only the one with the higher rec_index and only if it's is_modified = 0 (it's it's 1 than no need to show any of the "duplicated" files)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

It's a simple aggregate.

Have you read the introductory article [/url]yet?

Writing a few INSERT statements will take you seconds.

It will only take us seconds to write your query but without sample data it will be UNTESTED.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 7

with cte as (

select ROW_NUMBER() over(partition by File_desc order by File_desc,rec_index desc) as Ranks ,* from MyTable

where is_modified = 0

)

select * from cte where ranks =1

Jayanth Kurup[/url]

dardar4 SSC-Addicted Points: 403 More actions · Answer 8

ok, i added this code fro creating and inserting values to the table

CREATE TABLE [dbo].[MyTable]

(

[rec_index] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

file_desc nvarchar(50),

is_modified bit

)

insert into MyTable (rec_index, file_desc, is_modified) values (1,'a.txt',0)

insert into MyTable (rec_index, file_desc, is_modified) values (2,'a.txt',1)

insert into MyTable (rec_index, file_desc, is_modified) values (3,'b.txt',0)

insert into MyTable (rec_index, file_desc, is_modified) values (4,'b.txt',0)

i expect to get only the last record (the second record should not appear cause although it has higher rec_index it's is_modified = 1 )

dardar4 SSC-Addicted Points: 403 More actions · Answer 9

sorry this is the correct code

CREATE TABLE [dbo].[MyTable]

(

[rec_index] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

file_desc nvarchar(50),

is_modified bit

)

insert into MyTable (file_desc, is_modified) values ('a.txt',1)

insert into MyTable (file_desc, is_modified) values ('a.txt',0)

insert into MyTable (file_desc, is_modified) values ('b.txt',0)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Thanks for the sample data.

The simplest solution to this is the following:

SELECT

file_desc,

rec_index = MAX(rec_index)

FROM MyTable

WHERE is_modified = 0

GROUP BY file_desc

There's no need to incur the cost and complexity of OVER().

John M asked if this is a homework question - if it is, then be sure to fully understand any solution which is offered to you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

dardar4 SSC-Addicted Points: 403 More actions · Answer 11

thnx for the help but that didn't work

insert into MyTable (file_desc, is_modified) values ('a.txt',0)

insert into MyTable (file_desc, is_modified) values ('a.txt',1)

insert into MyTable (file_desc, is_modified) values ('b.txt',0)

SELECT

file_desc,

rec_index = MAX(rec_index)

FROM MyTable

WHERE is_modified = 0

GROUP BY file_desc

this code will result 2 results: the first row and the last one.

but i dont want the first row because although it's is_modified = 0 it is not the the one with the highest rec_index from all the a.txt files

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

dardar4 (8/17/2011)
thnx for the help but that didn't work
insert into MyTable (file_desc, is_modified) values ('a.txt',0)
insert into MyTable (file_desc, is_modified) values ('a.txt',1)
insert into MyTable (file_desc, is_modified) values ('b.txt',0)
insert into MyTable (file_desc, is_modified) values ('b.txt',0)
SELECT
file_desc,
rec_index = MAX(rec_index)
FROM MyTable
WHERE is_modified = 0
GROUP BY file_desc
this code will result 2 results: the first row and the last one.
but i dont want the first row because although it's is_modified = 0 it is not the the one with the highest rec_index from all the a.txt files

You've changed the spec:

dardar4 (8/17/2011)
...return only those that is_modified=0(i.e false) and if 2 records have the same file name return the one with the larger index value...

In fact, what you're now suggesting no longer makes sense.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 13

dardar4 (8/17/2011)
thnx for the help but that didn't work
insert into MyTable (file_desc, is_modified) values ('a.txt',0)
insert into MyTable (file_desc, is_modified) values ('a.txt',1)
insert into MyTable (file_desc, is_modified) values ('b.txt',0)
insert into MyTable (file_desc, is_modified) values ('b.txt',0)
SELECT
file_desc,
rec_index = MAX(rec_index)
FROM MyTable
WHERE is_modified = 0
GROUP BY file_desc
this code will result 2 results: the first row and the last one.
but i dont want the first row because although it's is_modified = 0 it is not the the one with the highest rec_index from all the a.txt files

OK, so you need a subquery (or CTE) that selects the max rec_index for each file_desc, regardless of the value of is_modified. Then select everything from that where is_modified is 0. Have a go at that and post back if there's anything you don't understand. You'll learn more if you try it yourself, and people are often more willing to help if you've made your own attempt.

John

dardar4 SSC-Addicted Points: 403 More actions · Answer 14

ok. let's start over

the object of my task is to do the following:

get all records that were not modified.

if 2 files or more have the same name we are looking at the one with the larger id and inspects id he was updated or not

insert into MyTable (file_desc, is_modified) values ('a.txt',0)

insert into MyTable (file_desc, is_modified) values ('a.txt',1)

insert into MyTable (file_desc, is_modified) values ('b.txt',0)

insert into MyTable (file_desc, is_modified) values ('c.txt',0)

insert into MyTable (file_desc, is_modified) values ('d.txt',1)

in this example:

row 1 and 2 will not be selected since we looking at row (it's the larger one) and we see that he is not 0.

from row 3 and 4 - row 4 will be selected cause it's larger and it's 0

row 5 will be selected

row 6 will not be selected

i hope this will clarify the issue

anyway, i succeeded making such an sql statement but it's damn ugly

select * from

(select * from MyTable as t2 where rec_index >= all (select rec_index from MyTable as t1 where t2.file_desc = t1.file_desc)) as t3

where t3.is_modified = 0

any suggestion how to make this more nice?

help with an sql problem

Cookies on SQLServerCentral