Get value of MAX(ID) into a variable

Hi All,

Hope someone can help a newbie!

I have the following code:

/* Get MAXID from tblHotels_Web to form the HotelID */SqlCommand cmdGetMaxID =new SqlCommand("Select MAX(HotelID) from tblHotels_Web");int intMaxID;

How do I get the value of HotelID into my intMaxID variable and populate txtID.Text?

Regards,

Brett

see thia example:

static public int AddProductCategory(string newName, string connString){ Int32 newProdID = 0; string sql = "Select MAX(HotelID) from tblHotels_Web"; using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand cmd = new SqlCommand(sql, conn); try { conn.Open(); newProdID = (Int32)cmd.ExecuteScalar(); } catch (Exception ex) { Console.WriteLine(ex.Message); } } return (int)newProdID;}

Get the Row which has Max value of the field

I want to get a field value in the row, which has max value of another field in the same table

I have done with the below query. I want to know is there any other better way of doing it becuase it is taking longer time.

Thanks for your help

SELECT Field1
FROM TableName
WHERE Field2 = ( SELECT MAX(field2)
FROM TableName)That shouldn't take too long to process. How many rows, and do you have an index on Field2?|||I have about 200k records. I have index (non-clustered )on field2. More over it is taking about 7 sec to run the above query

Thanks|||...and of those 200K records, how many are typically being returned in the result set? It may take several seconds to display the result set.

Have you tried viewing the query plan?

Get the max of the aggregates

Hello:

I've been researching a likely common problem with reporting services: the inability to get an aggregate of an aggregate. One particular discussion thread comes close to solving my specific problem:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2021871&SiteID=1

Here's my problem. I have a table that groups data per month based on Count(). I'd like to get the max(count()) -- i.e., which month has the highest count?

For example:

JAN 30

FEB 20

MAR 25

I'd like to identify the month that has the max count. In this case, I'd like to capture the aggregate value "30" as being the max value of the three months displayed.

My goal is to embed a horizontal stacked-bar chart into the table (to the left of the count() values). Various types of medical services are being counted per month: Inpatient Stay, Outpatient Service, PCP visit. The key to displaying the horizontal stacked-bar charts is to make sure the maximum value of the chart is the same for all charts -- i.e., I need to know which month has the highest count and then set that value as the max limit on the chart.

Robert Bruckner's technical article on "Get More out of SQL Server Reporting Services Charts" briefly touches on the topic of embedded charts in a table, but doesn't go into the level of detail I'm considering.

I've also come across related information from SSW Rules to Better Reporting Services. Similar to Robert's article, SSW doesn't address scaling an in-line chart based on data that is aggregated, but rather scaling the in-line chart based on the values found in a specified data field.

Ultimately, I'd like to create an in-line bar chart that appropriate shows the month of JAN as having the longest horizontal bar, and FEB/MAR having appropriately scaled smaller bars.

Thanks in advance!

--Pete

Why not create a second dataset that calcualtes the MAX of the COUNT in SQL or MDX or whatever you're using. This dataset would return only 1 row and 1 column and you can refer to this value by using =First(Fields!your_max_count_field.value, "dataset name") to scale your charts.

I realise that this is doubling up on the query and increases maintenance and that it's a workaround for a missing bit of functionality etc. etc. etc.

The point is it would work.

|||

I've thought about setting up a second dataset to achieve this, as you suggested, but can a chart embedded in a table actually reference a different dataset? i.e. -- if the table uses dataset "A", and an inline chart is displaying a stacked-bar chart using "A", can you actually set the max limit of the chart to dataset "B"? Seems like anytime I've ever attempted to drive a data region with values that are NOT part of a the same data region dataset, I get an error....

|||

There is no restriction in using values from a different dataset. The key part is to qualify the Aggregate call with a dataset name i.e.

= First(Fields!col1.Value, "some_other_dataset")

|||

Ah, I see... I'll shall give it a go. Sounds promising.

Thanks Adam,

--Pete

get the ID value from MAX value of a set of columns?

Hi to everybody
I'm having some trouble while trying to solve this issue.
I have this table:
DAY HOUR MINUTE
ID FIX_VALUE_A FIX_VALUE_B VARIABLE_C VARIABLE_D VARIABLE_E
103 897544 750916 26 17 04
165 897544 750916 25 17 13
203 897544 750916 25 17 02
514 334692 264553 15 03 46
517 334692 264553 16 03 43
1060 334692 264553 16 10 01
I need to group the rows with same FIX_VALUES and, from each group, get
the ID of the row with maximum set of VARIABLE_VALUES
Actually,
VARIABLE_VALUE_C = DAY
VARIABLE_VALUE_D = HOUR
VARIABLE_VALUE_E = MINUTE
and the three of them come from a SUBSTRING(TIMESTAMP_FIELD,X,Y).
I need to get the ID of the row with the highest set of
(DAY,HOUR,MINUTE)

>From the previous example, the expected results should be ID=103 and
ID=1060
Could anybody help me?
TIA,
David Grantselect ID, FIX_VALUE_A, FIX_VALUE_B
from T as T1
where not exists (
select * from T as T2
where T2.TIMESTAMP_FIELD > T1.TIMESTAMP_FIELD
)
If the TIMESTAMP_FIELD values aren't ordered correctly, you
can try
...
where not exists (
select * from T as T2
where T2.VARIABLE_C + T2.VARIABLE_D + T2.VARIABLE_E
> T1.VARIABLE_C + T1.VARIABLE_D + T1.VARIABLE_E
)
Without seeing the details, I'm doing some guessing about types, but
hopefully this will be helpful.
Steve Kass
Drew University
icebold54@.hotmail.com wrote:

>Hi to everybody
>I'm having some trouble while trying to solve this issue.
>I have this table:
> DAY HOUR MINUTE
>ID FIX_VALUE_A FIX_VALUE_B VARIABLE_C VARIABLE_D VARIABLE_E
>103 897544 750916 26 17 04
>165 897544 750916 25 17 13
>203 897544 750916 25 17 02
>514 334692 264553 15 03 46
>517 334692 264553 16 03 43
>1060 334692 264553 16 10 01
>
>I need to group the rows with same FIX_VALUES and, from each group, get
>the ID of the row with maximum set of VARIABLE_VALUES
>Actually,
>VARIABLE_VALUE_C = DAY
>VARIABLE_VALUE_D = HOUR
>VARIABLE_VALUE_E = MINUTE
>and the three of them come from a SUBSTRING(TIMESTAMP_FIELD,X,Y).
>I need to get the ID of the row with the highest set of
>(DAY,HOUR,MINUTE)
>
>ID=1060
>
>Could anybody help me?
>TIA,
>David Grant
>
>|||>> I need to get the ID of the row with the highest set of (DAY,HOUR,MINUTE)
One simple approach is:
SELECT *
FROM tbl t1
WHERE CAST( t1.day AS CHAR( 10 ) +
CAST( t1.day AS CHAR( 10 ) +
CAST( t1.day AS CHAR( 10 ) =
( SELECT MAX( CAST( t1.day AS CHAR( 10 ) +
CAST( t1.day AS CHAR( 10 ) +
CAST( t1.day AS CHAR( 10 ) )
FROM tbl t2
WHERE t2.fix_col1 = t1.fix_col2
AND t2.fix_col2 = t2.fix_col2 ) ;
Have you considered representing the value in a single DATETIME column?
Anith|||Try,
select
FIX_VALUE_A,
FIX_VALUE_B,
[id]
from
t1 as a
where
((((VARIABLE_C * 100) + VARIABLE_D) * 100) + VARIABLE_E) = (select
max((((b.VARIABLE_C * 100) + b.VARIABLE_D) * 100) + b.VARIABLE_E) from t1 as
b where b.FIX_VALUE_A = a.FIX_VALUE_A and b.FIX_VALUE_B = a.FIX_VALUE_B)
go
AMB
"icebold54@.hotmail.com" wrote:

> Hi to everybody
> I'm having some trouble while trying to solve this issue.
> I have this table:
> DAY HOUR MINUTE
> ID FIX_VALUE_A FIX_VALUE_B VARIABLE_C VARIABLE_D VARIABLE_E
> 103 897544 750916 26 17 04
> 165 897544 750916 25 17 13
> 203 897544 750916 25 17 02
> 514 334692 264553 15 03 46
> 517 334692 264553 16 03 43
> 1060 334692 264553 16 10 01
>
> I need to group the rows with same FIX_VALUES and, from each group, get
> the ID of the row with maximum set of VARIABLE_VALUES
> Actually,
> VARIABLE_VALUE_C = DAY
> VARIABLE_VALUE_D = HOUR
> VARIABLE_VALUE_E = MINUTE
> and the three of them come from a SUBSTRING(TIMESTAMP_FIELD,X,Y).
> I need to get the ID of the row with the highest set of
> (DAY,HOUR,MINUTE)
>
> ID=1060
>
> Could anybody help me?
> TIA,
> David Grant
>|||Thank your for your interest, Steve.
The table has this aspect:
ID FIXA FIXB FIXC FIXD VARE VARF VARG VARH VARI VAR J
107 36902 7187815 2004 8 2004 09 23 14 06 50
104 36902 7187815 2004 8 2004 09 15 17 06 50
101 36902 7187815 2004 8 2004 09 15 14 07 50
14 36902 7187815 2004 8 2004 09 15 14 06 50
98 75184 4066402 2004 8 2004 11 05 17 17 43
105 75184 4066402 2004 8 2004 10 05 17 17 43
102 75184 4066402 2004 8 2004 10 04 18 17 43
97 75184 4066402 2004 8 2004 10 04 17 19 50
15 75184 4066402 2004 8 2004 10 04 17 17 43
99 91006 9131800 2004 8 2004 10 24 11 40 04
106 91006 9131800 2004 8 2004 09 24 11 40 04
103 91006 9131800 2004 8 2004 09 23 12 40 04
100 91006 9131800 2004 8 2004 09 23 11 43 14
16 91006 9131800 2004 8 2004 09 23 11 40 04
where the FIX* are the FIXED_VALUES and thee VAR* are the
VARIABLE_VALUES that come from a previous
SUBSTRING(TIMESTAMP_FIELD,X,Y). In this table there's no timestamp
anymore.
The VARIABLE_VALUES stand for
VARE=YEAR
VARF=MONTH
VARG=DAY
VARH=HOUR
VARI=MINUTE
VARJ=SECOND
The self-join you proposed only got one value and unfortunately it
wasn't none from the expected three.
Do you have any idea about what could I do now?
TIA,
David Grant|||Should be :
...
WHERE CAST( t1.day AS CHAR( 10 ) +
CAST( t1.hour AS CHAR( 10 ) +
CAST( t1.minute AS CHAR( 10 ) =
( SELECT MAX( CAST( t1.day AS CHAR( 10 ) +
CAST( t1.hour AS CHAR( 10 ) +
CAST( t1.minute AS CHAR( 10 ) )
...
Anith|||Sorry Anith, but I must be doing something wrong with your solution
because the Query Analyzer is complaining about it. (Something like:
'Multiple columns have been specified in an aggregation expression that
contains an extern reference. If an aggregated expression contains an
extern reference, this must be the only column to be referred in the
expression')
Se han especificado m=FAltiples columnas en una expresi=F3n de agregado
que contiene una referencia externa. Si una expresi=F3n agregada
contiene una referencia externa, =E9sta deber=E1 ser la =FAnica columna a
la que se haga referencia en la expresi=F3n.
SELECT *
FROM REPEATDETECT t1
WHERE CAST(t1.day AS CHAR(10)) +
CAST(t1.hour AS CHAR(10)) +
CAST(t1.minute AS CHAR(10)) =3D
(SELECT MAX(CAST(t1.day AS CHAR(10)) +
CAST(t1.hour AS CHAR(10)) +
CAST(t1.minute AS CHAR(10)))
FROM REPEATDETECT t2
WHERE t2.FIX_VALUE_A =3D t1.FIX_VALUE_A
AND t2.FIX_VALUE_B =3D t2.FIX_VALUE_B
AND t2.FIX_VALUE_C =3D t2.FIX_VALUE_C
AND t2.FIX_VALUE_D =3D t2.FIX_VALUE_D)
Do you know what am I doing wrong?
TIA,
David Grant|||Alejandro,
Your solution is working good. Could you please be so kind to explain
me why does it work? I know you are doing a self-join and using the 2nd
'WHERE' clause to stablish a relationship between the two copies of the
table. However, at this moment I can't guess which is the objective
(apart from solving my problem, thankyou again) of all these
multiplications by 100.
TIA,
David Grant|||> SELECT *
> FROM REPEATDETECT t1
> WHERE CAST(t1.day AS CHAR(10)) +
> CAST(t1.hour AS CHAR(10)) +
> CAST(t1.minute AS CHAR(10)) =
> (SELECT MAX(CAST(t1.day AS CHAR(10)) +
> CAST(t1.hour AS CHAR(10)) +
> CAST(t1.minute AS CHAR(10)))
> FROM REPEATDETECT t2
> WHERE t2.FIX_VALUE_A = t1.FIX_VALUE_A
> AND t2.FIX_VALUE_B = t2.FIX_VALUE_B
> AND t2.FIX_VALUE_C = t2.FIX_VALUE_C
> AND t2.FIX_VALUE_D = t2.FIX_VALUE_D)
Use t2 instead t1 in the aggregation function.
SELECT *
FROM REPEATDETECT t1
WHERE CAST(t1.day AS CHAR(10)) +
CAST(t1.hour AS CHAR(10)) +
CAST(t1.minute AS CHAR(10)) =
(SELECT MAX(CAST(t2.day AS CHAR(10)) +
CAST(t2.hour AS CHAR(10)) +
CAST(t2.minute AS CHAR(10)))
FROM REPEATDETECT t2
WHERE t2.FIX_VALUE_A = t1.FIX_VALUE_A
AND t2.FIX_VALUE_B = t1.FIX_VALUE_B
AND t2.FIX_VALUE_C = t2.FIX_VALUE_C
AND t2.FIX_VALUE_D = t2.FIX_VALUE_D)
AMB
"icebold54@.hotmail.com" wrote:

> Sorry Anith, but I must be doing something wrong with your solution
> because the Query Analyzer is complaining about it. (Something like:
> 'Multiple columns have been specified in an aggregation expression that
> contains an extern reference. If an aggregated expression contains an
> extern reference, this must be the only column to be referred in the
> expression')
> Se han especificado múltiples columnas en una expresión de agregado
> que contiene una referencia externa. Si una expresión agregada
> contiene una referencia externa, ésta deberá ser la única columna a
> la que se haga referencia en la expresión.
>
> SELECT *
> FROM REPEATDETECT t1
> WHERE CAST(t1.day AS CHAR(10)) +
> CAST(t1.hour AS CHAR(10)) +
> CAST(t1.minute AS CHAR(10)) =
> (SELECT MAX(CAST(t1.day AS CHAR(10)) +
> CAST(t1.hour AS CHAR(10)) +
> CAST(t1.minute AS CHAR(10)))
> FROM REPEATDETECT t2
> WHERE t2.FIX_VALUE_A = t1.FIX_VALUE_A
> AND t2.FIX_VALUE_B = t2.FIX_VALUE_B
> AND t2.FIX_VALUE_C = t2.FIX_VALUE_C
> AND t2.FIX_VALUE_D = t2.FIX_VALUE_D)
>
> Do you know what am I doing wrong?
> TIA,
> David Grant
>|||Create an int number with the three values that we can use to compare,
instead comparing them individualy. Let see an example.
var_c = 26
var_d = 17
var_e = 04
((((var_c * 100) + var_d) * 100) + var_e) = 261704
26 * 100 = 2600
2600 + 17 = 2617
2617 * 100 = 261700
261700 + 04 = 261704
var_c = 25
var_d = 17
var_e = 13
((((var_c * 100) + var_d) * 100) + var_e) = 251713
now we can compare 261704 to 251713.
After reading your post to Steve, I would recommend to create a view that
recreate the datetime value, or a calculated column in the same table.
create view dbo.v1
as
select
[ID],
FIXA,
FIXB,
FIXC,
FIXD,
cast(VARE + '-' + VARF + '-' + VARG + 'T' + VARH + ':' + VARI + ':' + VARJ
+ '.000' as datetime) as dt_col
from
dbo.t1
go
Now you can use dt_col to do the comparison.
create view dbo.v1
as
select
[ID],
FIXA,
FIXB,
FIXC,
FIXD,
cast(VARE + '-' + VARF + '-' + VARG + 'T' + VARH + ':' + VARI + ':' + VARJ
+ '.000' as datetime) as dt_col
from
dbo.t1
go
select
FIXA,
FIXB,
FIXC,
FIXD,
[ID]
from
dbo.v1 as a
where
a.dt_col = (select max(b.dt_col) from dbo.v1 where b.FIXA = a.FIXA and
b.FIXB = a.FIXB and b.FIXC = a.FIXC and b.FIXD = a.FIXD)
-- or
select
a.FIXA,
a.FIXB,
a.FIXC,
a.FIXD,
a.[ID]
from
dbo.v1 as a
inner join
(
select
FIXA,
FIXB,
FIXC,
FIXD,
max(dt_col) as max_dt_col
from
dbo.v1
group by
FIXA,
FIXB,
FIXC,
FIXD
) as b
on b.FIXA = a.FIXA and b.FIXB = a.FIXB and b.FIXC = a.FIXC and b.FIXD =
a.FIXD and b.max_dt_col = a.dt_col
go
AMB
"icebold54@.hotmail.com" wrote:

> Alejandro,
> Your solution is working good. Could you please be so kind to explain
> me why does it work? I know you are doing a self-join and using the 2nd
> 'WHERE' clause to stablish a relationship between the two copies of the
> table. However, at this moment I can't guess which is the objective
> (apart from solving my problem, thankyou again) of all these
> multiplications by 100.
>
> TIA,
> David Grant
>

Get the 3 max age

Hi,
I have a table with
ID int,
Name varchar,
Age int
how could i make an stored procedure to get the three old persons from
database?
--
Thanks
Regards.
JosemaTry:
select top 3 * from TheTable
order by Age desc|||Wouldn't it make more sense to store date of birth rather than age? How
will you keep the Age column up to date?
SELECT TOP 3 WITH TIES id, name, age
FROM YourTable
ORDER BY age DESC
Presumably there could be several people with the same age so you may
well get more than 3 rows returned. Do you have a rule for the 3 you
want in the event of ties? You can add other columns to the ORDER BY
clause to narrow down the selection.
David Portas
SQL Server MVP
--|||Thanks bd,
Regards.
Josema
"bd" wrote:

> Try:
> select top 3 * from TheTable
> order by Age desc
>|||> Wouldn't it make more sense to store date of birth rather than age? How
> will you keep the Age column up to date?
Heh... run a job every day that joins against a linked server (e.g. the IRS
database) on SSN, and checks if their DOB has the same day and month as
today. I'm sure it gets even more interesting on Feb. 29. :-)|||>> Heh... run a job every day that joins against a linked server
And for those who are into astrological beliefs, the job has to be scheduled
to run every minute or perhaps every sec!
Anith

Get Output from one SP in to another SP

Hi I have an SP OmgngsVal
ALTER PROCEDURE OmgngsVal
@.SexVal nvarchar,
@.Sasong int

AS
BEGIN

SET NOCOUNT ON;

SELECT MAX(Omgang) AS [Omgng]
FROM Resultat
WHERE @.SexVal = Lag And @.Sasong = Ssong
END

And want [Omgng] as input in next SP

DECLARE
@.SexVal nvarchar,
@.Omgng INT,
@.Sasong int
SET @.SexVal='A'
SET @.Sasong=20072008
EXEC Ubc90OmgngsVal @.SexVal,@.Sasong

BEGIN
SELECT Match.MatchId, Match.matchdate AS MatchStart, Team.team AS Hemma, Team1.team AS Borta, Match.score, Match.vsscore
FROM Match INNER JOIN
Team ON Match.team = Team.TeamId INNER JOIN
(SELECT TeamId, team, GroupId
FROM Team AS Team_1) AS Team1 ON Match.vsteam = Team1.TeamId
WHERE Match.Omgng = @.Omgng

But i get error:
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '@.Omgng'.

B Regards
GertYour sproc doesn't return a value...
Take a look at the keyword OUTPUT when defining your sproc

ALTER PROCEDURE OmgångsVal
@.SexVal nvarchar,
@.Sasong int,
@.test int OUTPUT
AS
BEGIN
...|||Your derived table looks unnecessary.

And why not make it all in one pass?

SELECT Match.MatchId, Match.matchdate AS MatchStart, Team.team AS Hemma, Team1.team AS Borta, Match.score, Match.vsscore
FROM Match INNER JOIN
Team ON Match.team = Team.TeamId INNER JOIN
(SELECT TeamId, team, GroupId
FROM Team AS Team_1) AS Team1 ON Match.vsteam = Team1.TeamId
WHERE Match.Omgång = (SELECT MAX(Omgang)
FROM Resultat
WHERE @.SexVal = Lag And @.Sasong = Säsong)|||Don't use sprocs for variable assignments. Use user-defined functions instead:
Create function OmgngsVal
(@.SexVal nvarchar,
@.Sasong int)
returns int

AS
begin
return
(SELECT MAX(Omgang) AS [Omgng]
FROM Resultat
WHERE @.SexVal = Lag And @.Sasong = Ssong)
end

Call it like this:
DECLARE
@.SexVal nvarchar,
@.Sasong int
SET @.SexVal='A'
SET @.Sasong=20072008

SELECT Match.MatchId,
Match.matchdate AS MatchStart,
Team.team AS Hemma,
Team1.team AS Borta,
Match.score,
Match.vsscore
FROM Match
INNER JOIN Team ON Match.team = Team.TeamId
INNER JOIN --Team
(SELECT TeamId,
team,
GroupId
FROM Team) AS Team1
ON Match.vsteam = Team1.TeamId
WHERE Match.Omgng = dbo.OmgngsVal(@.SexVal, @.Sasong)|||Got error:
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ')'.

DECLARE
@.SexVal nvarchar,
@.Sasong int
SET @.SexVal='A'
SET @.Sasong=20072008

BEGIN
SELECT Match.MatchId, Match.matchdate AS MatchStart, Team.team AS Hemma,
Team1.team AS Borta, Match.score, Match.vsscore
FROM Match INNER JOIN
Team ON Match.team = Team.TeamId INNER JOIN
(SELECT TeamId, team, GroupId
FROM Team AS Team_1) AS Team1 ON Match.vsteam = Team1.TeamId
WHERE Match.Omgång =(SELECT MAX(Omgang)
FROM Resultat
WHERE @.SexVal = Lag And @.Sasong = Säsong)|||There's no syntax error. Did you copy and paste what you put in your post exactly as you had in the query analyser\ management studio?|||This probably isn't perfect, but it ought to get you started:-- ptp 200710111 See http://www.dbforums.com/showthread.php?t=1623252
GO
-- ptp 200710111 Return the highest Omgang value

CREATE FUNCTION dbo.dbforums_OmgångsVal (
@.SexVal nvarchar
, @.Sasong int)
RETURNS INT AS BEGIN

RETURN (SELECT MAX(Omgang) AS [Omgång]
FROM Resultat
WHERE @.SexVal = Lag
AND @.Sasong = Säsong)
END
GO
-- ptp 200710111 Procedure to demonstrate use of dbo.dbforums_OmgångsVal

CREATE PROCEDURE dbo.dbforums_otherprocedure
AS

DECLARE
@.SexVal NVARCHAR
, @.Omgång INT
, @.Sasong INT

SET @.SexVal='A'
SET @.Sasong=20072008
EXEC Ubc90OmgångsVal @.SexVal,@.Sasong

BEGIN
SELECT Match.MatchId, Match.matchdate AS MatchStart, Team.team AS Hemma, Team1.team AS Borta, Match.score, Match.vsscore
FROM Match INNER JOIN
Team ON Match.team = Team.TeamId INNER JOIN
(SELECT TeamId, team, GroupId
FROM Team AS Team_1) AS Team1 ON Match.vsteam = Team1.TeamId
WHERE Match.Omgång = dbo.dbforums_OmgångsVal(@.SexVal, @.Sasong)
END-PatP|||Now i use that Blindman wrote with user-defined functions
And it works fine.|||Possibly a bit of a dumb question blindman but does the optimiser defo recognise that the scaler function will always return the same value and so execute it only once? I think I recall once finding a problem and ended up putting a function like that into a derived table to make it explicit to SQL Server that it was not to run it once per row.|||Because none of the parameters for the UDF are derived from the recordsets (@.SexVal and @.Sasong are essentially constants for the duration of the transaction), the UDF should only be called once.|||does the optimiser defo recognise that the scaler function will always return the same value and so execute it only once?This is part of why Transact-SQL functions can only call deterministic functions. A deterministic function always returns the same value for a given set of arguments.

This means that for the duration of a transaction (and every SQL statement executed by Microsoft SQL Server is part of a transaction whether it is explicitly declared or not), a UDF will always return the same value for any given set of arguments.

Following that logic one step further, a UDF only needs to be called repeatedly if one of its arguments is a column. Constants and variables should not change within the context of a single Transact SQL statement, so there is no need to re-evaluate the function call.

-PatP|||Absolutely - I just wanted to check the optimser knew that. It should and I suspected it would - I just wasn't sure.

Get only single row results per id?

Hi, is it possible to make an sql query that has an Outer Join but return only one row of results max per id.

For example i have an Articles table, and a PicturesForArticles table.

The Articles table has an id field(aid), a title field(aTitle) and a content field(aContent).

And the PicturesForArticles table has an id field(pid), a PicPath filed and a field linking it to the articles table(aid)

Obviously the PicturesForArticles field stores pictures for the articles, and article can have a multiple number of pictures, or no pictures at all.

So i want to make a query that will return all of the Articles fields and a picture for each article. Even if the article has many pictures i only want to get a single row for each aid(Articles Id), and if there are no pictures for that article the picture fields will be null.

Is there any way to do this, to only return on row of results for each aid?

Thanks

That's very similar to something that I discussed in one of my articles on SingingEels :http://www.singingeels.com/Articles/How_To_Maintain_Customer_Payment_History.aspx

Basically, you can use the "SubSelect" method I did under the "Joining The Tables Together" subheading.

Let me know if you need more help with this, if not, then please mark this post as the answer.

Thanks,

|||

Did you mean the first query after the subheading?

If so then how can i select more than one field from the derived table, do i need another derived table?

|||

At the end of the article I show how to get more than one field from a 'vertical' table, but grouped by a certain criteria (in my case "CustomerID")

SELECT
Customers.*,
LatestPayments.*
FROM
dbo.CustomersLEFTOUTERJOIN
(SELECT CustomerID,MAX(ID)AS LastPaymentID,MAX(PaymentDate)AS LastPaymentDate
FROM dbo.PaymentHistoryGROUPBY CustomerID) LatestPayments
ON Customers.ID = LatestPayments.CustomerID

That's the code fromhttp://www.singingeels.com/Articles/How_To_Maintain_Customer_Payment_History.aspx that shows how to do that... if you are having troubles modifying it to your needs, then please past your table definitions and I'll change it to work for you.

Thanks,

|||

Ok thanks

Get only Max of each distinct?

Hello,

I have a table

ItemID Version

12 1.0
12 1.1
12 2.0
13 2.0
13 1.0
14 1.0
15 1.0
15 5.0
15 2.1

How do I write a Select query to get me all distinct item IDs, whichm
are of the latest version?

Like this:

ItemID Version
12 2.0
13 2.0
14 1.0
15 2.1

Any help would be appreciated.

Thankssunilkes@.gmail.com wrote:

Quote:

Originally Posted by

I have a table
>
ItemID Version
>
12 1.0
12 1.1
12 2.0
13 2.0
13 1.0
14 1.0
15 1.0
15 5.0

I assume 5.0 is a typo for 2.0

Quote:

Originally Posted by

15 2.1
>
How do I write a Select query to get me all distinct item IDs, whichm
are of the latest version?
>
Like this:
>
ItemID Version
12 2.0
13 2.0
14 1.0
15 2.1

This smells like homework. Look up MAX() and GROUP BY.|||On Nov 7, 10:10 am, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

sunil...@.gmail.com wrote:

Quote:

Originally Posted by

I have a table

>

Quote:

Originally Posted by

ItemID Version

>

Quote:

Originally Posted by

12 1.0
12 1.1
12 2.0
13 2.0
13 1.0
14 1.0
15 1.0
15 5.0

>
I assume 5.0 is a typo for 2.0
>

Got it, actually it was pretty simple, was trying it incorrectly
earlier !

SELECT MAX(Version_Number) AS Maxim, ItemId
FROM tblItems
GROUP BY ItemId

Thanks

Quote:

Originally Posted by

Quote:

Originally Posted by

15 2.1

Quote:

Originally Posted by

>

Quote:

Originally Posted by

How do I write a Select query to get me all distinct item IDs, whichm
are of the latest version?

>

Quote:

Originally Posted by

Like this:

>

Quote:

Originally Posted by

ItemID Version
12 2.0
13 2.0
14 1.0
15 2.1

>
This smells like homework. Look up MAX() and GROUP BY.

Get Max(ondate) but not as aggregate

I know there must be a better way....
What I have done in the past is to make each column a subselect
,ca.ondate = (select max(ondate) from ....
,cs.ondate = (select max(ondate from...
This appears to be a poor way and performance would suffer.
So, onto a new way, but it's not working for me...
Say I have a contacts, calendar and history tables, I want all records c.amp
=
'MIC' and if they exist the latest pending & completed appointment or sale f
or
each.
I want a left outer join to cal & hist.
I want the most recent calendar (pending) appointment if it exists where the
code = 'R' from the calendar table.
I want the most recent calendar (pending) sale if it exists where the code =
'R'
from the calendar table.
I want the most recent history appointment where the code = 'R'.
I want the most recent history sale where the code = 'R'.
What happens is say if there are no pending appt's, then I get no records
because of the exclusive where.
Here's the trash that I have so far... (remember I want all records but onl
y a
date value if it exists.
select c.contact
ca.ondate
cs.ondate
ha.ondate
hs.ondate
from contacts c
left outer join calendar ca on ca.id = c.id and ca.code = 'R'
left outer join calendar cs on cs.id = c.id and cs.code = 'R'
left outer join history ha on ha.id = c.id and ha.code = 'R'
left outer join history hs on hs.id = c.id and hs.code = 'R'
where ca.ondate in(select max(ondate) from calendar where id = ca.id and cod
e =
'R')
and cs.ondate in(select max(ondate) from calendar where id = cs.id and code
= 'R')
and ha.ondate in(select max(ondate) from history where id = ha.id and code =
'R')
and hs.ondate in(select max(ondate) from history where id = hs.id and code =
'R')
where c.amp = 'MIC'
TIA
JeffP...Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
The nature of time is a continuum, Remember Zeno, Einstein and all
those guys? You need to model the start and endding times of a state
or an event, something like this:
CREATE TABLE Events
(event_id INTEGER NOT NULL,
start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (event_id, start_time),
end_time DATETIME, -- null means still active
CHECK (start_time < end_time),
..);
Now use predicates like this:
my_date BETWEEN start_time
AND COALESCE(end_time, CURRENT_TIMESTAMP),
and a VIEW that finds the rows where (end_time IS NULL).
You will find that having a Calendar table will be useful. What you are
calling a Calendar seems to have some vague id instead of a calendar
date for a key and some kidn of equally vague code!|||In previous positions I worked with PeopleSoft quite a bit, and nearly every
table in the database had an effective date (EFFDT) column in it, and you
always had to take the record with the last EFFDT <= @.SomeDate.
Often this would mean 5 or more subselects, one for every table, usually
with @.SomeDate equal to the EFFDT from one of the other tables in the query.
The performance was surprisingly fast, as long as the table had the proper
index on (PrimaryKey, DateField).
With the proper indexes, I think the aproach you use below (with "=", not
"in") should be fine.
Of course, if you have already checked your indexes and you are still having
performance issues, then ignore this.
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:uDgtq02JGHA.1192@.TK2MSFTNGP11.phx.gbl...
> I know there must be a better way....
> What I have done in the past is to make each column a subselect
> ,ca.ondate = (select max(ondate) from ....
> ,cs.ondate = (select max(ondate from...
> This appears to be a poor way and performance would suffer.
> So, onto a new way, but it's not working for me...
> Say I have a contacts, calendar and history tables, I want all records
c.amp =
> 'MIC' and if they exist the latest pending & completed appointment or sale
for
> each.
> I want a left outer join to cal & hist.
> I want the most recent calendar (pending) appointment if it exists where
the
> code = 'R' from the calendar table.
> I want the most recent calendar (pending) sale if it exists where the code
= 'R'
> from the calendar table.
> I want the most recent history appointment where the code = 'R'.
> I want the most recent history sale where the code = 'R'.
> What happens is say if there are no pending appt's, then I get no records
> because of the exclusive where.
> Here's the trash that I have so far... (remember I want all records but
only a
> date value if it exists.
> select c.contact
> ca.ondate
> cs.ondate
> ha.ondate
> hs.ondate
> from contacts c
> left outer join calendar ca on ca.id = c.id and ca.code = 'R'
> left outer join calendar cs on cs.id = c.id and cs.code = 'R'
> left outer join history ha on ha.id = c.id and ha.code = 'R'
> left outer join history hs on hs.id = c.id and hs.code = 'R'
> where ca.ondate in(select max(ondate) from calendar where id = ca.id and
code =
> 'R')
> and cs.ondate in(select max(ondate) from calendar where id = cs.id and
code
> = 'R')
> and ha.ondate in(select max(ondate) from history where id = ha.id and
code =
> 'R')
> and hs.ondate in(select max(ondate) from history where id = hs.id and
code =
> 'R')
> where c.amp = 'MIC'
> TIA
> JeffP...
>|||you where clause effectively coverts your outer joins into inner
jouins.
Here is the correct way:
select 1 id
into #contacts
union all
select 2 id
union all
select 3 id
go
select 1 contact_id, '20060101' contact_date
into #dates1
union all
select 1, '20060103'
go
select 2 contact_id, '20060101' contact_date
into #dates2
union all
select 2, '20060103'
go
select c.id, cd1, cd2
from #contacts c
left outer join
(select contact_id, max(contact_date) cd1
from #dates1 group by contact_id) d1
on c.id = d1.contact_id
left outer join
(select contact_id, max(contact_date) cd2
from #dates2 group by contact_id) d2
on c.id = d2.contact_id
id cd1 cd2
-- -- --
1 20060103 NULL
2 NULL 20060103
3 NULL NULL
(3 row(s) affected)
go
drop table #contacts
drop table #dates1
drop table #dates2|||Thanks to all, I have very little control over the indexes, and rather than
pure
performance I was looking for this later answer using union by Alexander.
Also thanks to Jim as I have done similar on smaller bits of data, however t
his
qry will eventually be derived from a number of regional databases.
TIA
JeffP....
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1138830588.972004.263810@.g49g2000cwa.googlegroups.com...
> you where clause effectively coverts your outer joins into inner
> jouins.
> Here is the correct way:
> select 1 id
> into #contacts
> union all
> select 2 id
> union all
> select 3 id
> go
> select 1 contact_id, '20060101' contact_date
> into #dates1
> union all
> select 1, '20060103'
> go
> select 2 contact_id, '20060101' contact_date
> into #dates2
> union all
> select 2, '20060103'
> go
> select c.id, cd1, cd2
> from #contacts c
> left outer join
> (select contact_id, max(contact_date) cd1
> from #dates1 group by contact_id) d1
> on c.id = d1.contact_id
> left outer join
> (select contact_id, max(contact_date) cd2
> from #dates2 group by contact_id) d2
> on c.id = d2.contact_id
> id cd1 cd2
> -- -- --
> 1 20060103 NULL
> 2 NULL 20060103
> 3 NULL NULL
> (3 row(s) affected)
> go
> drop table #contacts
> drop table #dates1
> drop table #dates2
>|||Celko,
Where is there a more in-depth explanation of this? I really don't
understand the benefit to this aproach, but then again I don't really
understand the aproach itself. The only time I have seen this implemented
it was very difficult to retrieve records within a date range. On the other
hand I find the single date column to note the start of an event to be much
easier to work with.
Dates shown are in the format (month/day/year)
For example :
EmpID Event Effective Date Jobcode payrate
department
0034 Hire 1/15/2000 123
25.00 H23
0034 Promotion 3/20/2002 S15 36.00
H23
0034 Transfer 6/30/2005 S15 36.00
NTO
0034 Termination 8/30/2005 S15 36.00
NTO
keys would be EmpID and Effective Date (yes, they are out of order, its just
an example)
The event "Hire" takes place on 1/15/2000, and is not a date range, but a
single day. The employee is active from this day until the day before they
are terminated (8/29/2005). The termination date would mark the first day
that the employee is no longer active with the company.
There is a promotion event that occurs on 3/20/2002. Again the promotion
occurs that day, not over a period of time. Granted, the employee's
jobcode, department, etc, will be valid over a period of time, but the event
which causes the data to change is instantaneous.
I assume your aproach would require an entirely different table structure in
order to work, but I don't know how that would work.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138828873.001227.108770@.z14g2000cwz.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> The nature of time is a continuum, Remember Zeno, Einstein and all
> those guys? You need to model the start and endding times of a state
> or an event, something like this:
> CREATE TABLE Events
> (event_id INTEGER NOT NULL,
> start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> PRIMARY KEY (event_id, start_time),
> end_time DATETIME, -- null means still active
> CHECK (start_time < end_time),
> ..);
> Now use predicates like this:
> my_date BETWEEN start_time
> AND COALESCE(end_time, CURRENT_TIMESTAMP),
> and a VIEW that finds the rows where (end_time IS NULL).
> You will find that having a Calendar table will be useful. What you are
> calling a Calendar seems to have some vague id instead of a calendar
> date for a key and some kidn of equally vague code!
>

get max row size for every table

SQL Server 2000 has a max size of 8k per row limit. In an existing database,
how do I find out to get the tables that have this problem.
Is there a procedure that I can use or a sql script?
Magic
SELECT ob.name [Table Name], sum(col.length) [Max column Length]
from sysobjects ob, syscolumns col
where ob.id = col.id and ob.xtype = 'U'
group by ob.name
"Magic" <Magic@.discussions.microsoft.com> wrote in message
news:BC335A68-429E-4246-ABD9-55928A00F3AD@.microsoft.com...
> SQL Server 2000 has a max size of 8k per row limit. In an existing
database,
> how do I find out to get the tables that have this problem.
> Is there a procedure that I can use or a sql script?

get max row size for every table

SQL Server 2000 has a max size of 8k per row limit. In an existing database
,
how do I find out to get the tables that have this problem.
Is there a procedure that I can use or a sql script?Magic
SELECT ob.name [Table Name], sum(col.length) [Max column Length]
from sysobjects ob, syscolumns col
where ob.id = col.id and ob.xtype = 'U'
group by ob.name
"Magic" <Magic@.discussions.microsoft.com> wrote in message
news:BC335A68-429E-4246-ABD9-55928A00F3AD@.microsoft.com...
> SQL Server 2000 has a max size of 8k per row limit. In an existing
database,
> how do I find out to get the tables that have this problem.
> Is there a procedure that I can use or a sql script?

get max row size for every table

SQL Server 2000 has a max size of 8k per row limit. In an existing database,
how do I find out to get the tables that have this problem.
Is there a procedure that I can use or a sql script?Magic
SELECT ob.name [Table Name], sum(col.length) [Max column Length]
from sysobjects ob, syscolumns col
where ob.id = col.id and ob.xtype = 'U'
group by ob.name
"Magic" <Magic@.discussions.microsoft.com> wrote in message
news:BC335A68-429E-4246-ABD9-55928A00F3AD@.microsoft.com...
> SQL Server 2000 has a max size of 8k per row limit. In an existing
database,
> how do I find out to get the tables that have this problem.
> Is there a procedure that I can use or a sql script?

Get Max Date Query

I have the following 4 rows in a table

Company JobNumber BeginDate ModifyDate

1 2 12/12/2005 11/12/2006

1 2 12/12/2005 11/15/2006

2 3 11/12/2005 1/12/2006

2 3 11/12/2005 9/15/2006

The company and Job Number make up the key so yes this table has duplicate keys. My question is how would I return the two keys with the max modify date?

So the results would look like this:

1 2 12/12/2005 11/15/2006

2 3 11/12/2005 9/15/2006

Thanks,

SELECT Company, JobNumber, BeginDate, MAX(ModifyDate)

FROM blah

GROUP BY Company, JobNumber, BeginDate

|||

Thanks, works great!

GET MAX Date of previous year

Dear Friends,

I need to create a new column in a VIEW with a reference date to call from SQL Analysis Services 2005.

FactTable ID Name DateID Date 1 blabla… 8225 16-03-2005 2 blabla… 12-08-2006 … 9999 … … TIME Dimension DateID Day Year MonthKey Month QuarterKey Quarter 8224 15-03-2005 0:00 2005 20053 March 20051 Q1 8225 16-03-2005 0:00 2005 20053 March 20051 Q1 8226 17-03-2005 0:00 2005 20053 March 20051 Q1 8227 18-03-2005 0:00 2005 20053 March 20051 Q1 .. … … … … … … 9321 16-03-2008 0:00 2008 20083 March 20081 Q1 9322 17-03-2008 0:00 2008 20083 March 20081 Q1 9323 18-03-2008 0:00 2008 20083 March 20081 Q1 9324 19-03-2008 0:00 2008 20083 March 20081 Q1 9325 20-03-2008 0:00 2008 20083 March 20081 Q1 9326 21-03-2008 0:00 2008 20083 March 20081 Q1 9327 22-03-2008 0:00 2008 20083 March 20081 Q1 9328 23-03-2008 0:00 2008 20083 March 20081 Q1 9329 24-03-2008 0:00 2008 20083 March 20081 Q1 9330 25-03-2008 0:00 2008 20083 March 20081 Q1 9331 26-03-2008 0:00 2008 20083 March 20081 Q1 9332 27-03-2008 0:00 2008 20083 March 20081 Q1 9333 28-03-2008 0:00 2008 20083 March 20081 Q1 Result ID Name DateID Date RefDate 1 blabla… 8225 16-03-2005 25-12-2005 2 blabla… 12-08-2006 29-12-2006 … 9999 … … …

The refDate is the last VALID date from previous year for each date in each row of FactTable. My TimeTable only has valid dates (does not has holidays, saturday, sunday and forcedHolidays). so I need a query to get for each date in each row of FactTable the last date for previous year. Probably using the parameter "year" of the date in each row in facttable (using Datepart)

Someone help me?

Help me please!|||

Pedro,

To clarify: RefDate is the highest date from the Time Dimension table for a given year (dateid should be ignored)?

Also, you say "the last date for previous year" but your sample result shows the last date of the same year. Should DateID=8225 have 25-12-2004?

|||

Dalej you are write!!

I made a mistake, is

CORRECT Result ID Name DateID Date RefDate 1 blabla… 8225 16-03-2005 25-12-2004 2 blabla… 12-08-2006 29-12-2005 … 9999 … … …

The RefDate is the highest date from Time Table for a given year (for each date in each row)

Could help me?!

THANKS

|||

Code Snippet

select ft.*, td.RefDate

from FactTabl ft

innerjoin

(

select [Year],max([Day])as RefDate

from [Time Dimension]

groupby [Year]

)as td

on(ft.datepart(yy, Date)-1)= td.[Year]

|||

Dear alej,

I customize your statment to my database and there is an error...

Code Snippet

select ft.*, td.RefDate

from FactCashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(ft.datepart(yy, T_Dia)-1)= td.[Ano]

The error is:

Code Snippet

Msg 4121, Level 16, State 1, Line 1

Cannot find either column "ft" or the user-defined function or aggregate "ft.datepart", or the name is ambiguous.

When I was trying to customize your code I saw that I dont have the DATE in my FactTable, only the integer foreign key for time dimension. So, I created the FactCashFlow as a view to do a inner join to time table to get the date value.

If I can do it only in one view would be perfect!!

Could help me?
Thanks!!!

|||

OK!

I changed and this statment finally works:

Code Snippet

select ft.*, td.RefDate

from FactCashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, ft.T_Dia)-1)= td.[Ano]

But I have 2 views, and would be better using only one...

I need to change the the first select to get the date value from time dimension...

|||

In order to have only one view to call from SSAS, I need the query something like this:

Code Snippet

select ft.*, td.RefDate, MyTable.Dia

from(SELECT Dia FROM dbo.Time INNERJOIN dbo.CashFlows ON CF_RKData_ID=time.ID) MyTable INNERJOIN

CashFlows ft

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, ft.T_Dia)-1)= td.[Ano]

Code Snippet

Msg 102, Level 15, State 1, Line 11

Incorrect syntax near 'Ano'.

And this view will be the FactCashFlows in SSAS!

But this is wrong, could someone help me?

Thanks!

|||

OK I Found the solution...

Code Snippet

select ft.*, td.RefDate

from(dbo.time INNERJOIN CashFlows ft ON CF_RKData_ID=time.ID)

innerjoin

(

select [Ano],max([Dia])as RefDate

from [DimTime]

groupby [Ano]

)as td

on(datepart(yy, dbo.time.Dia)-1)= td.[Ano]

THANKS ALL!!!