|
Hi all,
I'm suprapto, I would like to ask how to query the table below,
based on the highest salary of each department. The table's name is Employee
EmpName Dept Salary
------- ------- -------
Blee Technic 10000000
Mawar Technic 2000000
Suprapto Prod 1000000
Edo Prod 2000000
P Thomas Technic 6000000
P Hank Prod 5000000
Bu Putri HR 20000000
Franky Technic 3500000
Rahel HR 4000000
So, what I expect to obtain for the result is like below:
EmpName Dept Salary
------- ------- --------
Blee Technic 10000000
Bu Putri HR 20000000
P Hank Prod 5000000
Could anyone help me to solve this problem ?
Your kind help is much appreciated.
Rgards,
|
|
|
|
|
For example something like:
SELECT *
FROM Employee e1
WHERE NOT EXITS (SELECT 1
FROM Employee e2
WHERE e2.Dept = e1.Dept
AND e2.Salary > e1.Salary)
|
|
|
|
|
The query is error, I'm working with SQL server 2005
|
|
|
|
|
There seems to be a typo in the text. Instead of NOT EXITS write NOT EXISTS. If you get another error, please include the error text in the post.
|
|
|
|
|
what is the meaning of unicode in sql?
|
|
|
|
|
waqarnaeem2@hotmail.com wrote: what is the meaning of unicode in sql?
The same as it means elsewhere I would presume. It is a form of character encoding.
|
|
|
|
|
well i have read that ,clustered index is 1 in table ,they match the physical & logical records, so what nonclustered index is more than 1 in a table, How they work?? then they increase the congestion 2much?
& y prefer 1 to another in what sense?
there is confusion.. plz reply
|
|
|
|
|
waqarnaeem2@hotmail.com wrote: there is confusion
Indeed there is. I'm having some difficulty parsing your question.
waqarnaeem2@hotmail.com wrote: clustered index is 1 in table
There can only be one clustered index in a table.
waqarnaeem2@hotmail.com wrote: they match the physical & logical records
The physical rows kind of follow the clustered index. It isn't exact, but the general idea is that the rows are physically sequenced by the clustered index however, SQL Server performs many optimisations that means that it doesn't follow that except in the simplest of circumstances.
waqarnaeem2@hotmail.com wrote: so what nonclustered index is more than 1 in a table
You can have many non-clustered indexes on a table.
waqarnaeem2@hotmail.com wrote: How they work??
The same way as an index in a book. The non-clustered area consists of a number of pages which contain look up information that can be used to find the physical rows faster than you would be able to if you had to scan the whole table to find the same information.
waqarnaeem2@hotmail.com wrote: then they increase the congestion 2much
What congestion? They will requires a little more IO for the lookup, but the idea is that the extra up-front work pays off as you can find the actual data much faster and using less IO overall. In some queries there may even be enough information in the index to never need to look at the physical rows at all. This makes everything much faster and less congested.
However, when you insert, update or delete from a table with non-clustered indexes you have to remember that SQL Server will also have to update the indexes as well as the actual physical row.
waqarnaeem2@hotmail.com wrote: y prefer 1 to another in what sense?
If you want more than one index on a table then you have no choice as you must use a non-clustered index at some point. Typically the clustered index is on your primary key
|
|
|
|
|
hi i am new in this world ,,
well the question is that .i have 3 tables
1)student
2)course
3)studentcourse
I want to retrieve the result all students name who enrolled in english & french but not in both.
|
|
|
|
|
This obviously is a homework... So what have you tried?
If you have encountered a problem, post the statement and describe the problem, then I believe that people are willing to guide you into the right direction, but no-one will do your homework for you.
|
|
|
|
|
|
Hi,
The problem is as follows:
I need a stored proc that can provide results on the maximum channel usage for any given period (either 24 hours, or n days period).
This is calculated by finding rows that overlap (by Datestamp + Duration) as follows:
ROW DATESTAMP [START] DATESTAMP + DURATION [END] CHANNEL
A 2009-01-02 12:11:27 2009-01-02 12:11:27 + 75 = 2009-01-02 12:12:42 9
B 2009-01-02 12:11:52 2009-01-02 12:11:52 + 20 = 2009-01-02 12:12:12 10
C 2009-01-02 12:11:55 2009-01-02 12:11:55 + 23 = 2009-01-02 12:12:18 6
Because rows B & C are active while row A is still active and the channel ids are unique, then 3 channels are simultaneously active.
If the duration of row C was 50 secs (for example) then only 2 channels would be active together (ie. 9 & 10).
The test data should produce the following results from the query
(For 24 Hours starting 2009-01-02 00:00:00)
PERIOD MAX CHANNELS ACTIVE
00:00 - 01:00 0
...
09:00 - 10:00 2
10:00 - 11:00 2
11:00 - 12:00 2
12:00 - 13:00 3
...
(For @numdays starting 2009-01-02)
DATE MAX CHANNELS ACTIVE
2009-01-02 3
2009-01-03 3
2009-01-04 4
...
Any ideas would be really really appreciated.
Thanks.
|
|
|
|
|
One question about the data.
munklefish wrote: This is calculated by finding rows that overlap (by Datestamp + Duration) as follows:
ROW DATESTAMP [START] DATESTAMP + DURATION [END] CHANNEL
A 2009-01-02 12:11:27 2009-01-02 12:11:27 + 75 = 2009-01-02 12:12:42 9
B 2009-01-02 12:11:52 2009-01-02 12:11:52 + 20 = 2009-01-02 12:12:12 10
C 2009-01-02 12:11:55 2009-01-02 12:11:55 + 23 = 2009-01-02 12:12:18 6
Because rows B & C are active while row A is still active and the channel ids are unique, then 3 channels are simultaneously active.
If the duration of row C was 50 secs (for example) then only 2 channels would be active together (ie. 9 & 10).
If row C has duration 50 secs which is 27 seconds more than in the example data, doesn't it still overlap between rows A and B. So why is it interpreted that it's not simultaneously active?
|
|
|
|
|
hi,
how to delete rows of two database tables with single delete query?
regards,
bill
|
|
|
|
|
"Not"
Delete works on a single table. You can wrap them in a transaction if you want them to 'act' is if they were a single statement.
I are troll 
|
|
|
|
|
Not true. It is possible to delete from a join of two or more tables, or implement cascade deletes via a constraint on a foreign key relation, or use a trigger to do more complex rule based deletes from additional tables based on the scope of the first delete.
|
|
|
|
|
|
If the tables (and the desired rows) are related by some common key (a primary key/foreign key relationship would be ideal) the you could delete the rows as a join on the two tables) :
delete from TableA inner join TableB on TableA.fielda1 = TableB.fieldB3
Alternatively, if it is SQL server, you could create a trigger on TableA that deleted rows from tableB basded on what was in the Deleted psuedo table at the time of the deletion from TableA.
|
|
|
|
|
Rob Graham wrote: you could delete the rows as a join on the two tables
If you're referring to the T-SQL extension on DELETE statement, it won't delete rows on both tables. It's just another way to express where condition.
|
|
|
|
|
Hey everybody
Does anyone have any idea how can I perform OutputDebugString (C#'s Trace) in SQL?
Is it event possible?
Thanks!
|
|
|
|
|
It depends, where do you want the output to go?
If you simply want the output to the client calling SQL Server stored procedure, you could use PRINT[^]
|
|
|
|
|
Hey,
Thanks for the quick reply!
I don't want to output to the calling app.
I want to print in DebugView (printing to the "output debug").
|
|
|
|
|
Could you explain a little bit more:
- are you using Visual Studio or SQL Server Management Studio?
- then you call stored procedure?
- while the stored procedure executes you want to print something in the Output window in Visual Studio or Messages Window in Management Studio
- if using Visual Studio, are you running a program and debugging it or simply using Server Explorer while in design mode
|
|
|
|
|
I'm using SQL Server Management to write stored procedures.
Than, I'm running application that calls these stored procedures (I'm not running the application with debugger).
Just like you wrote, I want to print something to the output window - print to DebugView (what C++'s OutputDebugString() or C#'s Trace.Writeline() are doing).
|
|
|
|
|
Okay, if you use PRINT in the stored procedure and execute the procedure in Management Studio, the output will come to the message window in Management Studio.
When you're executing an application which then calls a stored procedure, AFAIK there's no way to print any messages to a Management Studio window.
However, if you're using SqlConnection in the application, you can wire InfoMessage[^] event. Whatever you print in the procedure will come to this event and you can redirect it to the place you want.
Was this what you we're after?
If you want the output to the server side where SQL Server is running, you can:
- write the message to a table
- write it to the sqlserver log file
- write it to some other file
- send it using mail
- create a notification
modified on Saturday, January 31, 2009 5:36 PM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
