MAX and DATEDIFF: select

Showing posts with label select. Show all posts

Wednesday, March 21, 2012

maybe is a stupid question.. about CASE

I have a little query in access ..now i must write it in T-Sql

the original...
(MSG.IDLINGUA = IIF( EXISTS
(SELECT * FROM ESMESSAGGI AS MSG WHERE MSG.IDLINGUA = [@.idLingua] AND MSG.IDMESSAGGIO = BITS.IDDESCR),
[@.idLingua], LDEF.IDLINGUA))

I can't be able to assign a select value at my field... IDLINGUA to perform another select..
Of course I have used the "select case when exists"

Anyone could be help me ?Post the code you tried that failed.|||By the reference on your where clause to a table or alias called "Bits", it seems this is part of a larger query.
Please, post your whole query.

Monday, March 19, 2012

Maximum Value

Hey guys, I have this query which tries to get the maximum values from a table.

Code Snippet

SELECT scbcrse_subj_code,

MAX(scbcrse_eff_term), scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code

FROM Courses

WHERE scbcrse_csta_code = ''A''

GROUP BY scbcrse_subj_code, SCBCRSE_CRSE_NUMB, scbcrse_coll_code, scbcrse_csta_code, scbcrse_title

ORDER BY scbcrse_subj_code

Sample Table

scbcrse_subj_code scbcrse_eff_term scbcrse_crse_numb scbcrse_coll_code scbcrse_title ACCT 200620 4010 SB Advanced Accounting ACCT 200530 4010 SB Financial Accounting IV

Now, there is a column which is called scbcrse_title which shows the title of the course, which in this case on the table the titles are different. One is called Advanced Accounting and the other is called Financial Accounting IV. My question is, how can I only show the highest number on the scbcrse_eff_term? The problem occurs when there are different titles.

Does scbcrrse_title need to be part of the group by? Can you just group on crse_numb, coll_code, and subj_code only ? Then subselect the title using the three group by values where eff_term is max? Something like the below (i did not check the code for errors sorry)

Code Snippet

SELECT C.scbcrse_subj_code,

C.scbcrse_crse_numb,

C.scbcrse_coll_code,

C.scbcrse_csta_code,

MAX(C.scbcrse_eff_term),

(SELECT scbcrse_title

FROM Courses

WHERE scbcrse_subj_code = C.scbcrse_subj_code

AND scbcrse_crse_numb = C.scbcrse_crse_numb

AND scbcrse_coll_code = C.scbcrse_coll_code

AND scbcrse_csta_code = C.scbcrse_csta_code

AND scbcrse_eff_term) = (SELECT MAX(C.scbcrse_eff_term) FROM Courses WHERE C.Cscbcrse_csta_code = 'A' GROUP BY C.scbcrse_subj_code, C.SCBCRSE_CRSE_NUMB, C.scbcrse_coll_code, C.scbcrse_csta_code ) AS [Title]

FROM Courses C

WHERE C.Cscbcrse_csta_code = 'A'

GROUP BY C.scbcrse_subj_code, C.SCBCRSE_CRSE_NUMB, C.scbcrse_coll_code, C.scbcrse_csta_code

ORDER BY scbcrse_subj_code

|||Thanks Dave, I'll check the code to see if it works. And yes, the GROUP BY command does force me to include scbcrse_title.

|||

Maybe this:

Code Snippet

SELECT scbcrse_subj_code,

scbcrse_eff_term,

scbcrse_crse_numb,

scbcrse_coll_code,

scbcrse_title,

scbcrse_csta_code

FROM Courses c

inner join

(

SELECT scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code,

MAX(scbcrse_eff_term) as eff_term

FROM Courses

) selhigh

on c.scbcrse_subj_code = selhigh.scbcrse_subj_code

and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb

and c.scbcrse_coll_code = selhigh.scbcrse_coll_code

and c.scbcrse_eff_term = selhigh.scbcrse_eff_term

WHERE c.scbcrse_csta_code = 'A'

|||Diango,

You can do this without a GROUP BY as follows:

SQL Server 2000

SELECT scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses
WHERE scbcrse_csta_code = ''A''
AND NOT EXISTS (
SELECT * FROM Courses AS C
WHERE C.scbcrse_subj_code = Courses.scbcrse_subj_code
AND C.scbcrse_crse_numb = Courses.scbcrse_crse_numb
AND C.scbcrse_coll_code = Courses.scbcrse_coll_code
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
)
ORDER BY scbcrse_subj_code

In other words, choose all rows for Courses where the table does not contain a later (measured by eff_term) row with the same (subj_code, crse_numb, coll_code) combination. This assumes you want one result row for each (subj_code, crse_numb, coll_code) combination, and you can adjust the inner WHERE clause if this is not the set of columns that you want one row for each combination of.

In SQL Server 2005, you have another option:

SQL Server 2005

WITH Courses_ranked AS (
SELECT
scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb,
scbcrse_coll_code, scbcrse_title, scbcrse_csta_code,
RANK() OVER (
PARTITION BY scbcrse_subj_code, scbcrse_crse_numb, scbcrse_coll_code
ORDER BY scbcrse_eff_term DESC
) AS rk
)
SELECT
scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb,
scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses_ranked
WHERE rk = 1

Both solutions will return the "latest" row (or rows in the case of ties for latest) for each combination (scbcrse_subj_code, scbcrse_crse_numb, scbcrse_coll_code) in the table.

Steve Kass
Drew University
http://www.stevekass.com
|||The first solution almost worked but it had the problem that when there was a code that only had one value, it got omitted. The second solution I have never done before so I'm having some problems implementing it.|||I notice I left the = 'A' out of the inner query in the first solution. That could be it, but it could also be something about your data that you didn't mention. If you post some sample data and your adapted query that fails, I can take a look.

SK
|||That worked, you rock! As soon as I added the ''A'' in the inner join, it came back with the correct result.|||Steve, I don't mean to be a pain in the ass, but I have a new issue. I'm still a bit of a noob so bare with me. Your code worked great by the way, it did the job as intended. New update is required which I wasn't aware. I want to take into account those courses that do have '' I '' as well as the maximum value on the scbcrse_eff_term. I can do this easily by simply removing the WHERE clause where scbcrse_csta_code = ''A'' on both occasions. The thing is, for those courses that have the maximum value which have an '' I '' I need those courses removed completly. ( I don't mean delete those records that '' I '' , I just mean Omitting them some how.|||I hope I'm understanding, because you didn't give any specific example.

You want to see the latest row for each (subject code, course number, college code) from among those rows with csta code either 'A' or 'I', only if that latest row happens to be one of the 'A' rows. Note that if 'A' and 'I' are the only possible values of csta_code, you don't have to say WHERE scbcrse_csta_code IN ('A','I'). I think this will do it.

SELECT scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses
WHERE scbcrse_csta_code IN ('A','I')
AND NOT EXISTS (
SELECT * FROM Courses AS C
WHERE C.scbcrse_subj_code = Courses.scbcrse_subj_code
AND C.scbcrse_crse_numb = Courses.scbcrse_crse_numb
AND C.scbcrse_coll_code = Courses.scbcrse_coll_code
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
AND C.scbcrse_csta_code IN ('A','I')
)
AND Courses.scbcrse_csta_code = 'A'
ORDER BY scbcrse_subj_code
|||

Sorry, I should have explained myself a little better. Ok, this is your code:

AND C.scbcrse_csta_code = ''A''
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
)
ORDER BY scbcrse_subj_code

From the original code, we had a clause WHERE = ''A''. By having those two clauses, it returned the top value from scbcrse_eff_term Where scbcrse_csta_code = A, agreed? I found out later that the original requirement was wrong. The correct requirement was, get the highest scbcrse_eff_term as long as the class is active, or otherwise known as having a csta_code of ''A''. I know it sounds the same but let me explain further.

For example:

scbcrse_subj_code scbcrse_crse_numb scbcrse_eff_term scbcrse_csta_code

ACCT 4041 195600 A

ACCT 4041 200023 A

ACCT 4041 221457 I

From the original code, it should return the second row with a value of 200023 because it's the highest row that has a csta_code of ''A''. Here is where it changes, on the example, since the highest value for the class is 221457 and has a csta_code of '' I '' the class has become inactive and therefore ACCT 4041 must no longer show up in our query. So in essence, every class that has the highest scbcrse_eff_term with a value of csta_code '' I '' should not show up at all in our query return. So ACCT 4041 would not show up on our list.

|||

Code Snippet

SELECT scbcrse_subj_code,

scbcrse_eff_term,

scbcrse_crse_numb,

scbcrse_coll_code,

scbcrse_title,

scbcrse_csta_code

FROM Courses c

inner join

(

SELECT scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code,

MAX(scbcrse_eff_term) as scbcrse_eff_term

FROM Courses

) selhigh

on c.scbcrse_subj_code = selhigh.scbcrse_subj_code

and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb

and c.scbcrse_coll_code = selhigh.scbcrse_coll_code

and c.scbcrse_eff_term = selhigh.scbcrse_eff_term

WHERE c.scbcrse_csta_code = 'A'

|||Dale, that's not working for me. I'm getting some errors when I try to implement it. I get the error of it's not a group by function. I also think that the code would return the maximum value of the courses that have a csta_code value of ''A'', not taking into account '' I ''. Which I do want to take into account '' I '' , but if the maximum scbcrse_eff_term of a course has a csta_code of '' I '' the class should be omitted completely from the result. It's not just eliminating all the '' I '', but it's eliminating all the courses from the list that has a maximum scbcrse_eff_term with scbcrse_csta_code of '' I ''. I don't know if that makes sense to you.|||

See if this is better.

I forgot the group by in the subquery.

This will gather up all the courses with the most current date, then only keep the ones that are A.

My understanding from what you've been saying is that you want to disregard all entries for the class if its current row is an I.

This should do just that.

Code Snippet

SELECT scbcrse_subj_code,

scbcrse_eff_term,

scbcrse_crse_numb,

scbcrse_coll_code,

scbcrse_title,

scbcrse_csta_code

FROM Courses c

inner join

(

SELECT scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code,

MAX(scbcrse_eff_term) as scbcrse_eff_term

FROM Courses

GROUP BY scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code

) selhigh

on c.scbcrse_subj_code = selhigh.scbcrse_subj_code

and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb

and c.scbcrse_coll_code = selhigh.scbcrse_coll_code

and c.scbcrse_eff_term = selhigh.scbcrse_eff_term

WHERE c.scbcrse_csta_code = 'A'

|||

It doesn't bring back the desired result. Let me show you what I mean.

scbcrse_subj_code scbcrse_crse_numb scbcrse_eff_term scbcrse_csta_code

ACCT 4041 195600 A

ACCT 4041 200023 A

ACCT 4041 221457 I

This is an example of a class that has gone inactive. This table has been poorly designed, which is why it's so challenging to create the proper effect. As you can see from this class, it has become inactive. When we run your query, it returns for example ACCT 4041 200023 and a code of A. The desired result is that this course doesn't come back at all because the maximum value for it is 221457 and because it has a scbcrse_csta_code of '' I ''.

Steve's code is great because it really can eliminate duplicates and it also brings the highest value of a course no matter what csta_code it has, because I removed the two WHERE clauses. Now, from the result set, I need to remove the courses that have a maximum value with a csta_code of '' I '' combination.

Monday, March 12, 2012

Maximum Numbers of Tables and colums to access in select statement

I want to get the
Maximum Numbers of Tables and colums to access in select query statement

Regards
AruneshCitations from SQL Books Online:columns: The maximum number of expressions that can be specified in the select list is 4096

maximum number of tables per select?

Does anyone know what is the maximum number of tables per select statement
in Oracle and SQL Server 2000?
Thanks a lot,
Lixin
I don't know it for Oracle, but for SQL Server 2000 it's 256.
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||I don't know it for Oracle, but for SQL Server 2000 it's 256.
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||SQL Server 2000: 256
For Oracle: you probably want to check in an Oracle group for that one.
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||SQL Server 2000: 256
For Oracle: you probably want to check in an Oracle group for that one.
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||That would be 256. Here are all the max capacities for SQL Server.
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||That would be 256. Here are all the max capacities for SQL Server.
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||Thanks all of you guys.
256 limitation caused me problem. I will post separately.
Lixin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb%23SMoVXEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I don't know it for Oracle, but for SQL Server 2000 it's 256.
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
statement
>
|||Thanks all of you guys.
256 limitation caused me problem. I will post separately.
Lixin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb%23SMoVXEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I don't know it for Oracle, but for SQL Server 2000 it's 256.
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
statement
>

maximum number of tables per select?

Friday, March 9, 2012

Maximum length of return in Query Analyzer

I am executing a SELECT statement that has about 500 characters of literal characters concatenated with the contents of a field from a table. I am then storing the result to be run as dynamic SQL. I am finding that when run this as select statement in query analyzer, the last part of the literal gets truncated. When I run it as a cursor and store it in a varchar(1000) variable and print the variable everything works fine. In addition when I put the select statement in a stored procedure and return this to a ADO recordset, the resultset is fine as well. But running the stored procedure in query analyzer truncates the results as well. The issue seems to be getting the results of the SELECT in query analyzer. Even running the stored procedure in the SQL area of Enterprise Manager returns a proper result. Has anyone heard of a maximum return from a select in query analyzer?Yes, check out: Tools, Options, the Results-tab. "Maximum characters per column".|||Yes, and I should probably post a FAQ entry for it.

1. Shift-Ctrl-o for options
2. Click the Results tab
3. Near the middle, Maximum characters per column..

-PatP|||Manj Tak
Danke Sha
merci beaucoup
mucho gracias|||Yes, and I should probably post a FAQ entry for it.

-PatP

Can you put one on there for making DBA coffee?

1. 4 heaping tbsp coffee grounds/8oz water
2. Pinch of salt
3. Add water tapped from boiler/radiator
4. Brew in pot that's never been washed using yesterday's filter
5. Serve in cup that's never been washed

Thanks,

hmscott

Maximum length of a select statement??

Hi there,
I am a novice. I need some information regarding what could be the maximum length of a select statement, esp the where clause.there is no actual limit. Possible limit would be what your programming language can store in a string.|||A limit that I ran into was using stored procedures where you pass a variable.

If you need to pass a variable or use a declared variable in the procedure you will be limitted by the 8000 character variable limit.

I have had times when I was passing a "WHERE" clause as a variable and reached the 8000 character limit. I haven't found another way to do it within a stored procedure yet, so I have been passing the SQL statement directly to the database. This was an ASP application pulling historical records. I had to use OR rather than Between becuase of some other criteria.

Otherwise the SQL statement passed directly to the database has no hard limit as far as I know.

Maximum Length Issue

Hi ,
I perform the query show below to find the duplicate records within the
table itself
"SELECT f_table.flt_Id, f_table.Psg_Id, f_table.Flt_dt_Id
FROM
[SELECT
Count(F_Sgmt_History.flt_Id),
Count(F_Sgmt_History.Psg_Id),
Count(F_Sgmt_History.Flt_dt_Id),
F_Sgmt_History.flt_Id, F_Sgmt_History.Psg_Id, F_Sgmt_History.Flt_dt_Id
FROM F_Sgmt_History
GROUP BY F_Sgmt_History.flt_Id, F_Sgmt_History.Psg_Id,
F_Sgmt_History.Flt_dt_Id
HAVING (((Count(F_Sgmt_History.flt_Id))>1) AND
((Count(F_Sgmt_History.Psg_Id))>1) AND
((Count(F_Sgmt_History.Flt_dt_Id))>1))]. AS f_table INNER JOIN F_Sgmt_History
ON (f_table.Flt_dt_Id = F_Sgmt_History.Flt_dt_Id) AND (f_table.Psg_Id =
F_Sgmt_History.Psg_Id) AND (f_table.flt_Id = F_Sgmt_History.flt_Id)"
But I get the following error
"Server: Msg 103, Level 15, State 7, Line 3
The identifier that starts with 'SELECT
Count(F_Sgmt_History.flt_Id),
Count(F_Sgmt_History.Psg_Id),
Count(F_Sgmt_History.Flt_dt_Id),
F_Sgmt_History.flt_Id, ' is too long. Maximum length is 128.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'. "
I am not sure wat's wrong with the statment , please help
Travis Tan
instead of '[' ,']' (square baracket ) use '(' ,')' and do not use '.'
before 'AS'.
your problem will be solved
"Travis" ?? ????:

> Hi ,
> I perform the query show below to find the duplicate records within the
> table itself
> "SELECT f_table.flt_Id, f_table.Psg_Id, f_table.Flt_dt_Id
> FROM
> [SELECT
> Count(F_Sgmt_History.flt_Id),
> Count(F_Sgmt_History.Psg_Id),
> Count(F_Sgmt_History.Flt_dt_Id),
> F_Sgmt_History.flt_Id, F_Sgmt_History.Psg_Id, F_Sgmt_History.Flt_dt_Id
> FROM F_Sgmt_History
> GROUP BY F_Sgmt_History.flt_Id, F_Sgmt_History.Psg_Id,
> F_Sgmt_History.Flt_dt_Id
> HAVING (((Count(F_Sgmt_History.flt_Id))>1) AND
> ((Count(F_Sgmt_History.Psg_Id))>1) AND
> ((Count(F_Sgmt_History.Flt_dt_Id))>1))]. AS f_table INNER JOIN F_Sgmt_History
> ON (f_table.Flt_dt_Id = F_Sgmt_History.Flt_dt_Id) AND (f_table.Psg_Id =
> F_Sgmt_History.Psg_Id) AND (f_table.flt_Id = F_Sgmt_History.flt_Id)"
> But I get the following error
> "Server: Msg 103, Level 15, State 7, Line 3
> The identifier that starts with 'SELECT
> Count(F_Sgmt_History.flt_Id),
> Count(F_Sgmt_History.Psg_Id),
> Count(F_Sgmt_History.Flt_dt_Id),
> F_Sgmt_History.flt_Id, ' is too long. Maximum length is 128.
> Server: Msg 156, Level 15, State 1, Line 3
> Incorrect syntax near the keyword 'AS'. "
> I am not sure wat's wrong with the statment , please help
> --
> Travis Tan

Saturday, February 25, 2012

Maxdop hint

Is parallelism only triggered for select statements or do
inserts/updates/deletes also go thru parallelism such as
update a
set col1 = 1
from a join b
on a.col2=b.col3 ............
or
delete from a join b on a.col1=b.col2 ........
If so, and if we want to disable parallelism, how can we do so for
deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
hint at the end of the statement ?From Books Online , "Degree of Parallelism" topic
"The INSERT, UPDATE, and DELETE operators are executed serially; however,
the WHERE clause of either an UPDATE or DELETE, or SELECT portion of an
INSERT statement may be executed in parallel. The actual data changes are
then serially applied to the database."
You can disable parallelism for INSERT, UPDATE and DELETE where relevant, in
the same way as for SELECT, with OPTION (MAXDOP 1).
--
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23U6JVjvSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Is parallelism only triggered for select statements or do
> inserts/updates/deletes also go thru parallelism such as
> update a
> set col1 = 1
> from a join b
> on a.col2=b.col3 ............
> or
> delete from a join b on a.col1=b.col2 ........
> If so, and if we want to disable parallelism, how can we do so for
> deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
> hint at the end of the statement ?
>
>
>|||Parallelism can also be set globally for the server in SEM>
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23U6JVjvSFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Is parallelism only triggered for select statements or do
> inserts/updates/deletes also go thru parallelism such as
> update a
> set col1 = 1
> from a join b
> on a.col2=b.col3 ............
> or
> delete from a join b on a.col1=b.col2 ........
> If so, and if we want to disable parallelism, how can we do so for
> deletes/updates.. I guess Im asking if i just place the option (maxdop 1)
> hint at the end of the statement ?
>
>
>

MAX_LENGTH Field does not match the value displayed?

Hi,

I wonder if anyone can help. I have installed the adventureWorksDW and when I run this query

select * from sys.all_columns where object_id = 85575343 and column_id = 2

The max_length filed value is 50 but if I view the column in explorer view the max_length is 25 I.e. ProductAlternateKey(nvarchar(25) ,null)

I don;'t know if I am looking at the correct table value?

Hi METAJOB,

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying.

tosc

|||

AdventureWorks is created using unicode (nchar, nvarchar, ntext).

Each 'single' character requires TWO bytes for storage. Therefore a nvarchar(25) field will require 50 bytes for storage. Max_length reports the actual storage size in bytes.

max(x) and top n

hello

i need a select than return max of top 10

for example top 10 return

5-9-6-10-78-2

i need max of them

how can i use max(x) in top n?

Try it like this

SELECT MAX(column1)FROM (SELECT TOP 10 column1FROM myTableORDER BY column2)AS A

|||

SELECTMAX(colA)AS maxColAFROM(SELECTROW_NUMBER()OVER(ORDERBY colBDESC)AS rownumber, ColAFROM yourTable)AS t1

WHERE rownumber<= 10

max(keyfield)

I forget, does SQLServer have an internal optimization, such that if
you have a (clustered) index on fields A and B, and you do a select
where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
the max?
Thanks.
J.A clustered index is an index but with the data at the leaf level. So if A
is the only or first column in the index (clustered or not) it can determine
what the max is with a seek and not a scan.
--
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.com> wrote in message
news:pouhp01v0onje4a3tgserrtb1ijr8bljd7@.4ax.com...
>I forget, does SQLServer have an internal optimization, such that if
> you have a (clustered) index on fields A and B, and you do a select
> where max(A) = 'X', SQLServer does NOT have to do a scan to figure out
> the max?
> Thanks.
> J.
>|||In addition to Andrew's points, I have a question. What kind of query is
>> where max(A) = 'X'
' Do you expect this to return a row? How? MAX() is an aggregate
function that works on all rows, while WHERE is a clause that works on
individual rows. Maybe you can show us a more practical query ...
--
http://www.aspfaq.com/
(Reverse address to reply.)|||On Mon, 15 Nov 2004 14:33:57 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>In addition to Andrew's points, I have a question. What kind of query is
>> where max(A) = 'X'
>' Do you expect this to return a row? How? MAX() is an aggregate
>function that works on all rows, while WHERE is a clause that works on
>individual rows. Maybe you can show us a more practical query ...
OK, that was a little terse, try something like:
select name
from mytable
where trxdate = (select max(trxdate) from mytable)
So a seek is better than a scan, anyway, and it can do this even for a
clustered index because the top index page has a last row. I just
wondered if it might do even better and cache the high value, but the
important thing is that you confirm it at least an avoid the full
scan. Though, now that I look at my real code again, I suspect it's
going to scan, anyway, oh well, at least I can improve my education
from this example.
Thanks.
J.|||> So a seek is better than a scan, anyway, and it can do this even for a
> clustered index because the top index page has a last row. I just
> wondered if it might do even better and cache the high value,
No, I don't think individual column values can be cached the way you are
describing.
However, for your education, wouldn't it be relatively simple to set up a
simple test?
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX f ON Kerplunk(foo)
GO
SET NOCOUNT ON
-- <<< populate data here! >>
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
GO
DROP TABLE Kerplunk
GO|||On Mon, 15 Nov 2004 15:18:06 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>> So a seek is better than a scan, anyway, and it can do this even for a
>> clustered index because the top index page has a last row. I just
>> wondered if it might do even better and cache the high value,
>No, I don't think individual column values can be cached the way you are
>describing.
Just the high and low.
>However, for your education, wouldn't it be relatively simple to set up a
>simple test?
Well, yeah, I guess, but sometimes it feels more productive to ask
than to do the reverse engineering, and some happy lurker might
benefit thereby.
>CREATE TABLE Kerplunk
>(
> foo INT,
> bar INT,
> raboof INT
>)
>GO
>CREATE CLUSTERED INDEX f ON Kerplunk(foo)
>CREATE INDEX f ON Kerplunk(foo)
on (bar), I suppose you meant.
>GO
>SET NOCOUNT ON
>-- <<< populate data here! >>
>-- hit Ctrl+K to see execution plan
>-- it will show where scans/seeks are used...
>SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
>SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM Kerplunk)
>GO
>DROP TABLE Kerplunk
>GO|||> Just the high and low.
My statement remains as is.
> Well, yeah, I guess, but sometimes it feels more productive to ask
> than to do the reverse engineering, and some happy lurker might
> benefit thereby.
Perhaps, but unless we know every single detail about your environment,
you're only going to get educated guesses.
> on (bar), I suppose you meant.
Yes, I did.|||On Mon, 15 Nov 2004 17:41:19 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>> Just the high and low.
>My statement remains as is.
>> Well, yeah, I guess, but sometimes it feels more productive to ask
>> than to do the reverse engineering, and some happy lurker might
>> benefit thereby.
>Perhaps, but unless we know every single detail about your environment,
>you're only going to get educated guesses.
>> on (bar), I suppose you meant.
>Yes, I did.
CREATE TABLE Kerplunk
(
foo INT,
bar INT,
raboof INT
)
GO
CREATE CLUSTERED INDEX f ON Kerplunk(foo)
CREATE INDEX b ON Kerplunk(bar)
GO
-- <<< populate data here! >>
SET NOCOUNT ON
declare @.ix int
set @.ix = 0
while @.ix < 100000
begin
insert into Kerplunk values (@.ix, @.ix, @.ix)
set @.ix = @.ix + 1
end
-- hit Ctrl+K to see execution plan
-- it will show where scans/seeks are used...
SELECT * FROM Kerplunk WHERE foo = (SELECT MAX(foo) FROM Kerplunk)
-- .00640
SELECT * FROM Kerplunk WHERE bar = (SELECT MAX(bar) FROM Kerplunk)
-- .00650
SELECT * FROM Kerplunk WHERE raboof = (SELECT MAX(raboof) FROM
Kerplunk)
-- 1.13
GO
DROP TABLE Kerplunk
GO

Monday, February 20, 2012

MAX() for non-HEX numbers

Hi,
I sit with a situation where I want to select the biggest number of a
field (varchar) which does NOT have a HEX Number in this field eg:
field
1
2
3E
A5
4
I would like to get 4 returned.
Any ideas ?
Thanx
Technically, 1, 2 and 4 are hex (as well as base 10 and octal). Perhaps you
want to consider only those values that do not have non-numerics?
Try:
select
max (field)
from
MyTable
where
field not like '%[~0-9]%'
Now, if you want numerical comparison of the values, then I'd change this
to"
select
max (cast (field as int))
from
MyTable
where
field not like '%[~0-9]%'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"AlexC" <alex.caudron@.mail.co.za> wrote in message
news:1130238860.255680.184090@.g43g2000cwa.googlegr oups.com...
Hi,
I sit with a situation where I want to select the biggest number of a
field (varchar) which does NOT have a HEX Number in this field eg:
field
1
2
3E
A5
4
I would like to get 4 returned.
Any ideas ?
Thanx

MAX() for non-HEX numbers

Hi,
I sit with a situation where I want to select the biggest number of a
field (varchar) which does NOT have a HEX Number in this field eg:
field
--
1
2
3E
A5
4
I would like to get 4 returned.
Any ideas ?
ThanxTechnically, 1, 2 and 4 are hex (as well as base 10 and octal). Perhaps you
want to consider only those values that do not have non-numerics?
Try:
select
max (field)
from
MyTable
where
field not like '%[~0-9]%'
Now, if you want numerical comparison of the values, then I'd change this
to"
select
max (cast (field as int))
from
MyTable
where
field not like '%[~0-9]%'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"AlexC" <alex.caudron@.mail.co.za> wrote in message
news:1130238860.255680.184090@.g43g2000cwa.googlegroups.com...
Hi,
I sit with a situation where I want to select the biggest number of a
field (varchar) which does NOT have a HEX Number in this field eg:
field
--
1
2
3E
A5
4
I would like to get 4 returned.
Any ideas ?
Thanx

MAX() for non-HEX numbers

Hi,
I sit with a situation where I want to select the biggest number of a
field (varchar) which does NOT have a HEX Number in this field eg:
field
--
1
2
3E
A5
4
I would like to get 4 returned.
Any ideas ?
ThanxTechnically, 1, 2 and 4 are hex (as well as base 10 and octal). Perhaps you
want to consider only those values that do not have non-numerics?
Try:
select
max (field)
from
MyTable
where
field not like '%[~0-9]%'
Now, if you want numerical comparison of the values, then I'd change this
to"
select
max (cast (field as int))
from
MyTable
where
field not like '%[~0-9]%'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"AlexC" <alex.caudron@.mail.co.za> wrote in message
news:1130238860.255680.184090@.g43g2000cwa.googlegroups.com...
Hi,
I sit with a situation where I want to select the biggest number of a
field (varchar) which does NOT have a HEX Number in this field eg:
field
--
1
2
3E
A5
4
I would like to get 4 returned.
Any ideas ?
Thanx

Wednesday, March 21, 2012

maybe is a stupid question.. about CASE

Monday, March 19, 2012

Maximum Value

Monday, March 12, 2012

Maximum Numbers of Tables and colums to access in select statement

maximum number of tables per select?

maximum number of tables per select?

maximum number of tables per select?

Friday, March 9, 2012

Maximum length of return in Query Analyzer

Maximum length of a select statement??

Maximum Length Issue

Saturday, February 25, 2012

Maxdop hint

MAX_LENGTH Field does not match the value displayed?

max(x) and top n

max(keyfield)

Monday, February 20, 2012

MAX() for non-HEX numbers

MAX() for non-HEX numbers

MAX() for non-HEX numbers

MAX and DATEDIFF

Blog Archive

About Me