|
Query for Getting Max number.
For Example: I have Department and Employee
For DeptId 1: Emp No 12,16,19
and For 2: 18,21,17
For 3: 27,28,16
I have to get Max(EmpNo) For department. If I want to get Max(EmpNo) for Dept(1,2)
output will be 19,21.
How can I get this using sqlserver2005
|
|
|
|
|
using row_number and partition
SELECT DeptId ,EmpNo
FROM (SELECT
*,
ROW_NUMBER() OVER(PARTITION BY DeptId ORDER BY EmpNo DESC)HighID FROM tableName) X
WHERE X.HighID = 1
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you so much. Its working fine ...
|
|
|
|
|
See the replies from Luc and Goutam, my answer is NOT the simplest way.
Comes from applying the wrong tool for the job.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Why not in this simple way
SELECT DEPTID, MAX(EMPNO) FROM TABLENAME GROUP BY DEPTID
|
|
|
|
|
that looks overly complex. wouldn't the following work (I think it does in MySQL):
SELECT DeptId,Max(EmpNo) 'maxEmpNo' FROM table1 GROUP BY DeptId ORDER BY DeptId
|
|
|
|
|
Hi. I am creating a form that will show results of a query in a "Datasheet View" - multiple records on one form with a header at the top of each column of data.
I want to know which event can i use to react to a new record being "loaded"?
My desire is to display a different label (text control) only when a field's value (date) changes by record. If I have several records, i want a visual indicator showing change in dates (one label for all Day-1's data, then only one (new) label for all Day-2's data, then only one (new) label for all Day-3's data, ....)
Thanks.
Johnny J
|
|
|
|
|
From my understanding of Access, you can't have multiple header while displaying data in "Datasheet View", there is only one set of headings at the top of the grid.
You can change the background color of cells to give the effect of a "green bar" report making the Day 1 data stand out from the Day 2 data. I believe you want to play around with the Events "Before Render", Before Layout, etc.
I've never done it with a datasheet view, but I have created Reports where I would BOLD dates of tasks that were late. The event in the report I believe are "On Print".
Good luck. 
|
|
|
|
|
Thanks for the advice.
I added a text control, separate from the record "set" to which the rest of the form is related. I just shoved the other controls to the right so that I can squeeze my text control to look like it occupies it's own column.
My idea was to have my new text control filled with the date, for that group of records with the same date. Only showing that date once.
I have my own function that identifies the date for each record and could do something like MyTextCtrl.value = strDateLabel (when I figure out how to react to the right Event.)
Thanks again, i will give your ideas a try.
JJM
|
|
|
|
|
Ah ha. I think I see what you are doing.
How about this ...
When you are building your dataset with a query, add a column where you can calculate a value you want displayed.
For example:
SELECT Payroll.WKNum, IIf([wkNum] Mod 2=0,"Even","Odd") AS OddEven
FROM Payroll;
This query displays the Payroll Week Number and a Text value of "Even or Odd"
This would free the DataSheet View from having to do any logic processing on the dataset, it would just display it.
If you need more complex logic, maybe you could extend this by manipulate a temporary table, then display that contents.
Give it a shot.
|
|
|
|
|
I am trying to pack four address lines, to leave no blank between the lines and no null values.
The code below is so horrid, it could qualify quite well to the coding horror forum.
This is also a question so I post it here.
The question is :
- How would you write this more elegantly ?
It would be fun to compare various other ways, I will perhaps post some
DECLARE @line1 AS VARCHAR(255)
DECLARE @line2 AS VARCHAR(255)
DECLARE @line3 AS VARCHAR(255)
DECLARE @line4 AS VARCHAR(255)
SET @line1 = '12 orchard st '
SET @line2 = NULL
SET @line3 = ' '
SET @line4 = 'BATH'
SET @line1 = RTRIM(LTRIM(COALESCE(@line1,'')))
SET @line2 = RTRIM(LTRIM(COALESCE(@line2,'')))
SET @line3 = RTRIM(LTRIM(COALESCE(@line3,'')))
SET @line4 = RTRIM(LTRIM(COALESCE(@line4,'')))
DECLARE @cpt INTEGER
WHILE @line1 = '' AND @cpt<3
BEGIN
SET @line1 = @line2
SET @line2 = @line3
SET @line3 = @line4
SET @line4 = ''
SET @cpt = @cpt + 1
END
SET @cpt = 0
WHILE @line2 = '' AND @cpt<2
BEGIN
SET @line2 = @line3
SET @line3 = @line4
SET @line4 = ''
SET @cpt = @cpt + 1
END
IF @line3 = ''
BEGIN
SET @line3 = @line4
SET @line4 = ''
END
PRINT 'Line 1:' + @line1
PRINT 'Line 2:' + @line2
PRINT 'Line 3:' + @line3
PRINT 'Line 4:' + @line4
|
|
|
|
|
If you want to just make a single string then there is a solution
DECLARE @line1 AS VARCHAR(255)
DECLARE @line2 AS VARCHAR(255)
DECLARE @line3 AS VARCHAR(255)
DECLARE @line4 AS VARCHAR(255)
DECLARE @line5 AS VARCHAR(2000)
SET @line1 = '12 orchard st '
SET @line2 = NULL
SET @line3 = ' '
SET @line4 = 'BATH'
SET @line5 = (SELECT RTRIM(LTRIM(COALESCE(@line1,''))) +
CASE WHEN LEN(RTRIM(LTRIM(COALESCE(@line2,'')))) > 0 THEN CHAR(13) ELSE '' END + RTRIM(LTRIM(COALESCE(@line2,''))) +
CASE WHEN LEN(RTRIM(LTRIM(COALESCE(@line3,'')))) > 0 THEN CHAR(13) ELSE '' END + RTRIM(LTRIM(COALESCE(@line3,''))) +
CASE WHEN LEN(RTRIM(LTRIM(COALESCE(@line4,'')))) > 0 THEN CHAR(13) ELSE '' END + RTRIM(LTRIM(COALESCE(@line4,''))))
PRINT @line5
|
|
|
|
|
Then you would need
SET @line1 = SUBSTRING(line5, [something here], [something here])
SET @line2 = SUBSTRING(line5, [something here], [something here])
SET @line3 = SUBSTRING(line5, [something here], [something here])
SET @line4 = SUBSTRING(line5, [something here], [something here])
Wouldn't you?
|
|
|
|
|
Well, thats why i said in my post If you want it in a single string.
and then this will not solve your purpose
|
|
|
|
|
Why do you insist on 4 variables, printing Patras solution will achieve the same output
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
a bubble-sort approach could solve that; in pseudo-code:
do {
bool more=false;
if (line1==empty && line2!=empty) {line1=line2; line2=empty; more=true;}
if (line2==empty && line3!=empty) {line2=line3; line3=empty; more=true;}
if (line3==empty && line4!=empty) {line3=line4; line4=empty; more=true;}
while(more);
|
|
|
|
|
Hi,
I'm Using:
SELECT * FROM OPENROWSET(BULK 'G:\1.jpg', SINGLE_BLOB)
to save binary files from hard disk to sql server database
Now I need an SQL code to retrieve binary file from Sql Server database to hard disk
|
|
|
|
|
|
I just SELECT it and use a FileStream to write it. Here's an example:
byte[] content = (byte[]) ds [ 1 ].Rows [ i ] [ "FileContent" ] ;
fs.Write ( content , 0 , content.Length ) ;
fs.Close() ;
ds is a DataSet, and fs is the FileStream.
|
|
|
|
|
that is not really streaming anything, so I'd recommend a simple File.WriteAllBytes().
BTW: I'm a bit puzzled by the ds[1] part...
|
|
|
|
|
Luc Pattyn wrote: File.WriteAllBytes().
In the case I quoted, I had to check whether or not a file by the chosen name existed first. There's no point creating the file if it already exists.
So I use a FileInfo to check Exists and simply use its Open method to get a FileStream.
Luc Pattyn wrote: the ds[1] part
refers to the oneth DataTable in the DataSet -- the set of files I want to create.
|
|
|
|
|
|
I am using a SQL statement to fill the contents of a subform's RecordSource:
Me.sub_NestedSchedule.Form.RecordSource = strMySqlStatement
How can I create a "virtual table" that has the empty string for all 6 of its fields and force that into my subform's RecordSource?
I want to initialize the subform during OnLoad and have the empty string.
Thanks,
JJM
|
|
|
|
|
Not sure how you are using the sql string but if you query the table with a nonsense where clause you will get back and empty data table.
Select * from MyTable where IDField = 0
Select * from MyTable where 1=1
These will return an empty datatable
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Select * from MyTable where 1=1
I think the previous SQL statement will return all rows in table
I think this is what you want to say
Select * from MyTable where 1=2
In addition to those solutions we can use
Select top 0 * from MyTable
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
