minimum value required in a column for each row.

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

Other Forums

SQL Server 6.5 \ SQL Server 7.0

minimum value required in a column for each row.

Author

Topic

koolkaus
Starting Member

9 Posts

Posted - 2015-03-19 : 12:27:42

Need help with a quick one. I need to create a query to get a value against each row suggesting whether the AppID was uploaded first time (Upload_D). this should result in 1 = firt time, 0 = more than first time.

Current Structure of Table:

ToDotype UID AppID Upload_D End_D_T

ToDotype = type of work (5 categories)
UID= unique number.
AppID= a non unique text
Upload_D = an integer or date suggesting time when an entry was created in DB.
END_D_T = time when a user striked it off from their list.

Rgds,
Kaus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-19 : 12:31:29

It's important to clarify which SQL Server version you are using since you posted in the SQL Server 6.5/7.0 forum.

Also, please post sample data and expected result set.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-19 : 12:45:04

Start with this approach

select t1.*, case when t2.upload_D is null then 0 else 1 end as first_time from table as t1
LEFT JOIN
(
select AppId,min(upload_D) as upload_D from table group by AppId
) as t2
on t1.AppId=t2.AppId and t1.upload_D=t2.upload_D

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 13:04:18

Do you just want to have a column in your query result to indicate if Upload_D is the minimum value for a given AppID ?


SELECT AppID,
       Upload_D,
       CASE WHEN EXISTS (SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D < T1.Upload_D)
            THEN 0 ELSE 1 END AS IsFirst
FROM MyTable AS T1

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 13:06:05

Madhi's code is much the same

@Madhi: I wonder if the JOIN to a sub-query with GROUP BY is more efficient than an EXIST in the SELECT clause?

koolkaus
Starting Member

9 Posts

Posted - 2015-03-19 : 14:59:56

Thank you folks. I will try these suggestions.

Rgds,
Kaus

koolkaus
Starting Member

9 Posts

Posted - 2015-03-20 : 00:59:41

My apologies first of all as this is related to MS Access 2010.
I am trying to shoot an SQL query through Excel.

It seems that the Case When statement is not supported by Access 2010 and an alternate is Switch. I tried using the above from Kristen however no success...

If I write it with Switch then is it correct to say:

SELECT AppID,
Upload_D,
SWITCH (SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D < T1.Upload_D, 1,SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D >= T1.Upload_D,0 ) AS IsFirst
FROM MyTable AS T1
?

Rgds,
Kaus

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-20 : 02:34:07

quote:
Originally posted by koolkaus

My apologies first of all as this is related to MS Access 2010.
I am trying to shoot an SQL query through Excel.

It seems that the Case When statement is not supported by Access 2010 and an alternate is Switch. I tried using the above from Kristen however no success...

If I write it with Switch then is it correct to say:

SELECT AppID,
Upload_D,
SWITCH (SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D < T1.Upload_D, 1,SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D >= T1.Upload_D,0 ) AS IsFirst
FROM MyTable AS T1
?

Rgds,
Kaus

Can you use IIF?

select t1.*, IIF(t2.upload_D is null,0,1) as first_time from table as t1
LEFT JOIN
(
select AppId,min(upload_D) as upload_D from table group by AppId
) as t2
on t1.AppId=t2.AppId and t1.upload_D=t2.upload_D

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-20 : 02:36:54

quote:
Originally posted by Kristen

Madhi's code is much the same

@Madhi: I wonder if the JOIN to a sub-query with GROUP BY is more efficient than an EXIST in the SELECT clause?

Yes I think so. Because the EXISTS has be executed for each and every row joining back to the same table. Not sure if it matters if you use different small table.

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2015-03-20 : 03:46:35

Does the JOIN's Sub Query GROUP BY have to process the whole table, and then selectively JOIN, or is it smart enough to be correlated?

I could do a test

but hopefully you know the answer off the top of your head?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-20 : 05:00:28

quote:
Originally posted by Kristen

Does the JOIN's Sub Query GROUP BY have to process the whole table, and then selectively JOIN, or is it smart enough to be correlated?

I could do a test but hopefully you know the answer off the top of your head?

In my test, my version uses hash join which is internally doing co-relation. But I think TEST must have tested this already

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2015-03-20 : 06:30:16

Yeah, but TEST is old so already forgotten what the result was

koolkaus
Starting Member

9 Posts

Posted - 2015-03-20 : 06:54:19

Thank you again. I am trying the query suggested at Posted - 03/20/2015 : 02:34:07 by Madhivanan.
Using a small table is not an alternate. This is an output from an existing "legacy" project. Cannot change DBs as software depends on it which is too big to change..

Rgds,
Kaus

koolkaus
Starting Member

9 Posts

Posted - 2015-03-20 : 07:14:44

Got a message that reads:
Circular reference caused by alias 'Upload_DT' in query definition's list.

I narrowed down the query to only
select AppId,min(upload_D) as upload_D from table group by AppId

& still received the same message. is this because we are using upload_D twice?

Also, while I was reading the query (please consider I am bit too novice in writing these SQLs), I was not sure if it is written to provide the below:
1) Same number of Rows
2) 1 Additional Columns that provides 0 or 1, lets say: First_Time
FActor to consider:
Upload_D = when an entry is keyed into the system. the idea is to know first_time when an app_id was uploaded.

Life is beautiful cause of helpful people like you all. Thank You for all the efforts.

Rgds,
Kaus

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-20 : 07:28:14

Do not use * in SELECT Statement. Explicitely type out the columns needed

select t1.AppId, IIF(t2.upload_D is null,0,1) as first_time from table as t1
LEFT JOIN
(
select AppId,min(upload_D) as upload_D from table group by AppId
) as t2
on t1.AppId=t2.AppId and t1.upload_D=t2.upload_D

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-20 : 07:30:49

quote:
Originally posted by Kristen

Yeah, but TEST is old so already forgotten what the result was

Good to see you again after a long time

Madhivanan

Failing to plan is Planning to fail

huangchen
Starting Member

37 Posts

Posted - 2015-04-02 : 05:53:29

unspammed

newwaysys
Starting Member

9 Posts

Posted - 2015-04-13 : 05:57:16

It seems that the Case When statement is not supported by Access 2010 and an alternate is Switch. I tried using the above from Kristen however no success...

If I write it with Switch then is it correct to say:

SELECT AppID,
Upload_D,
SWITCH (SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D < T1.Upload_D, 1,SELECT * FROM MyTable AS T2 WHERE T2.AppID = T1.AppID AND T2.Upload_D >= T1.Upload_D,0 ) AS IsFirst
FROM MyTable AS T1
?
ecently I generate Code 39 barcode in Reporting Service with this barcode tool unspammed
Because the EXISTS has be executed for each and every row joining back to the same table. Not sure if it matters if you use different small table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources