|
|
i have a file_no field in my accounting_payment table and i want to run a query to see the frequency of every file, means how many times every file is listed in the table?
|
|
|
|
|
use the count() and group by operators
Select count(*),file_no from accounting_payment group by file_no
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
But I also want to assign the Top number#? For example
Select TOP 5 count(*), file_no from accounting_payment
But to show the TOP (frequency) not the Top (position)
Similarly I want to do it for the amount, for example:
Select TOP 5 max(amount_paid), file_no from accounting_payment
|
|
|
|
|
Do some research in BOL into rownumber() and partition. Using these and a sub select you can get the top N records grouped by yourcriteria and ranked according to your sort requirements.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
May be you can do like this,
SELECT TOP 5 FREQUENCY, FILE_NO
FROM
(SELECT COUNT(*) FREQUENCY, FILE_NO
FROM ACCOUNTING_PAYMENT
GROUP BY FILE_NO) A
ORDER BY FREQUENCY DESC
|
|
|
|
|
I'm doing a bit of searching about these ID's and came across this gem from an Oracle FAQ[^].
If you allow people to log in with OPS$ accounts from Windows Workstations, you cannot be
sure who they really are. With terminals, you can rely on operating system passwords, with
Windows, you cannot.
In the absense of an example, how does one intrepret this. Windows passwords are unreliable? What could someone possibly mean by that? 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
The author was a nix nerd and nothing windows based has any validity. I wonder when that was written
I assume the OPS$ id is like the sa in sql server and should not be available to users. Still the statement as such does not make sense.
"If you set REMOTE_OS_AUTHENT=TRUE in your init.ora file, Oracle assumes that the remote OS has authenticated the user."
This seems to imply they do not trust the authentication of the remote OS, surprise, surprise.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: I wonder when that was written
Oracle is still from a world far behind. Even the latest version cannot be installed into C:\Program Files\.
So I guess it was written back in the Windows 3.1 era.
|
|
|
|
|
I'm fetching records from a table as shown below
<b>ID Name PID</b>
1 Ram 10
1 Ram 20
2 Shyam 30
2 Shyam 40
2 Shyam 50
3 Rohit 60
But i want to display records as shown below i. mean to show common name for similar fields
<b>ID Name PID</b>
1 Ram 10
- - 20
2 Shyam 30
- - 40
- - 50
3 Rohit 60
|
|
|
|
|
The relevant word here is display, you are asking the database to do the work of the presentation layer, that is not the database job. Use your presentation layer to do this formating.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
So I am working with some legacy data trying to clean it up and move it into a data mart.
Scenario
5 files of transactions varying from 10k to 10m rows with 100 columns (yeah I know a disgusting number of columns but I'm still working on it)
I load the csv file into a staging table using BCP with all columns as varchar.
Update the dimension tables
Working with the smallest table 13k rows. Using a select statement with joins to the dimension table to get the FK ids I have 10 inner joins so far.
The Issue
To reduce the response time I added TOP 100 to the select statement and was horrified to get a result in 2:26, when I remove the TOP 10 the execution time DROPS to 16 seconds. I always thought top N wold reduce the response time not increase it by orders f magnitude!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I guess it makes a sort of the set before picking the TOP 100.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
If I had an order by clause I would expect that but there is no order by.
I wonder if SQL 2008 has some implicit ordering (usually the first column in the select statement) that is impacting the query?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It depends on the query plan. But you're having some joins so I would assume so.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
I have around 12 joins and adding more as the day dissapears.
I am not bothered about the query plan at this point as I still designing the procedure. I was just astonished that the TOP 10 increases the response time!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If you are doing table scans in there, this will have an impact as your code has to read all the rows to determine what the top 100 actually are. The QEP will identify where the problems are.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
My blog | My articles | MoXAML PowerToys | Onyx
|
|
|
|
|
My assumption is that without and order by clause it should not have to scan the table, just get the first 10 records. It was during development so the performance was not really relevant as I had to tune the indexes after stabilising the query. I was just astonished it was slower than the full select.
Transforming this data in is going to take about 6 hours by the look of it, thankfully it is monthly not daily!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hey all,
I'm writing a basic parametrized query to search a database and am attempting to allow a user to specify 1 or 2 parameters for a search. However, I am having trouble when the user specifies both parameters as the return of the query then displays 0 rows. Here is what I have:
WHERE ((suburb LIKE '%' + @suburb1 + '%' AND @suburb2 ='') OR (suburb LIKE '%' + @suburb1 + '%' AND suburb LIKE '%' + @suburb2 + '%'))
@suburb1 and @suburb2 are declared within that statement as parameters for the query (I am using VS2008 to construct it). It looks like I need an IF statement or similar to make it do the first part if @suburb2 = '', however I can not for the life of me work out how to do this...
I am relatively new to writing queries, so I understand that I may be way off with how I'm approaching this, but some guidance in the right direction would be greatly appreciated!
Joe
|
|
|
|
|
Do some research into parameterised queries[^], this will allow you much greater flexibility and control and reduce the risk of sql injection attacks.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Just look at:
suburb LIKE '%' + @suburb1 + '%' AND suburb LIKE '%' + @suburb2 + '%'
If @suburb2 is different from @suburb1, that will select exactly 0 rows because suburb is either like @suburb1 or like @suburb2 but surely not like both at the same time. Use OR instead of AND here.
To differentiate between @suburb2 being set or not, use a CASE statement.
|
|
|
|
|
|
I hate it when someone univotes a post with no explanation.
You error is that this is a database forum and you are asking a question that requires VBA, I know you think you are using a database - Access but that is because Access should no longer be called a database, abomination maybe but not a database.
You will get a much better response in the VB forum. You should also mention that you are using spit MS Access and the version as there are differences that may be critical.
A possible answer - define some global variables representing the data, load them from the dataset/recordset or make the dataset/recordset a global. Sorry have not used Access for more than a decade.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
when i should use nvarchar(max) type and when not?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
